MySQL discrete notes-foreign key _ MySQL

MySQL discrete notes-foreign key Mysql foreign key

References: about MySQL foreign keys and mysql foreign key association creation

MySQL has the same definition of "key" and "index", so foreign keys and primary keys are also an index. The difference is thatMySQL automatically indexes the primary keys of all tables, but the foreign key field must be explicitly indexed by the user. // Check the Mysql manual and find that the INDEX is automatically created from MySQL 4.1.2.

Code for creating an instance with a foreign key: employee and payroll:

/*

Create employee table

*/

create table employees ( 

id int(5) not null auto_increment , 

name varchar(8) not null, 

primary key (id)

) 

type＝innodb;

/*

Create payroll

*/

create table payroll( 

id int(5) not null, 

emp_id int(5) not null, 

name varchar(8) not null, 

payroll float(4,2) not null, 

primary key(id), 

index emp_id (emp_id),

foreign key (emp_id) references employees (id)

)

type = innodb;

Referentialintegrity (Referentialintegrity) is widely used through the use of the foreign key (foreign key). It is supported by the new InnoDB list engine in MySQL. To establish a foreign key relationship between two MySQL tables, the following three conditions must be met:

* The two tables must be InnoDB tables.

* The fields using the foreign key relationship must be indexed ).

* The fields using the foreign key relationship must be of the same data type.

No wonder an error occurred when I tried to create a table with a foreign key. it turns out that the previous table is not of the InnoDB type, and the type of the table is determined:

Alter table table-name TYPE = INNODB;

Warning exists. After show warnings, it turns OUT that The "The syntax 'type = storage_engine 'is deprecated and will be removed in MySQL 6.0. Please use 'engine = storage_engine' instead" TYPE is OUT ~ I will use the Engine later.

For more information about table types (type/engine), see:

MySQL table type, MySQL engine/type InnoDB/MYISAM/MERGE/BDB/HEAP

Note:

• All tables in the link must be innoDB tables. in non-InnoDB tables, MySQL will ignore foreign key... REFERENCES modifier.
• Fields used for foreign key relationships must be explicitly indexed in all reference tables. InnoDB cannot automatically create indexes.
• In the foreign key relationship, the data type of the field must be similar, which is especially important for integer types that both the size and symbol must match.
• Even if the table has a foreign key constraint, MySQL allows us to delete the table without generating an error (even if this operation may damage the foreign key created earlier)

How to delete a foreign key:

ALTER TABLE table-name DROP FOREIGN KEY key-id;

Here is a concept. what is the id of this foreign key? We can use the show create table command to obtain the key-id value.

/*

Displays the table creation statement. The key-id is payroll_ibfk_1.

*/

show create table payroll /G

/*

*************************** 1. row ***************************

       Table: payroll

Create Table: CREATE TABLE `payroll` (

  `id` int(5) NOT NULL,

  `emp_id` int(5) NOT NULL,

  `name` varchar(8) NOT NULL,

  `payroll` float(4,2) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `emp_id` (`emp_id`),

  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*/

Automatic key update and deletion:

MySQL may forward... The REFERENCES modifier adds an on delete or on update clause to simplify tasks. it tells the database how to handle isolated tasks in this case.

Keywords	Description
CASCADE	Deleting all records that have reference relationships with the deleted key value is to delete the foreign key record.
SET NULL	Modify all records that have reference relationships with deleted key values and replace them with NULL values (only for fields marked as not null)
RESTRICT	Reject the deletion request until the secondary table that uses the deleted key value is manually deleted and has no reference (this is the default setting and the safest setting)
NO ACTION	Nothing

Note that when you set MySQL to perform automatic operations through the on update and on delete rules, if the key relationship is not set properly, it may cause serious data damage.