-- drop  table p_table;
-- drop  table c_table;

CREATE TABLE p_table ( -- Parent table
  id int(11) NOT NULL auto_increment,
  p_name varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  char set = utf8 ;


CREATE TABLE `c_table` ( -- Child table
  `id` int(11) NOT NULL auto_increment,
  `c_name` varchar(20) DEFAULT NULL,
  `p_id` int(11) NOT NULL ,
  PRIMARY KEY (`id`),
  foreign key fkeyName(p_id) references p_table(id)
) ENGINE=InnoDB  char set = utf8 ;

insert into p_table(p_name) values('Zhang 1');
insert into p_table(p_name) values('Zhang 2');
insert into p_table(p_name) values('Zhang 3');
select  *  from p_table ; -- 3 Bar record
1	Zhang 1
2	Zhang 2
3	Zhang 3


insert into c_table(c_name,p_id) values('Sub 1',1); -- Success
insert into c_table(c_name,p_id) values('Sub 2',2); -- Success
select  *  from c_table ;-- 2 Bar record
1	Sub 11 
2	Sub 22

insert into c_table(c_name,p_id) values('Sub 5',5); -- fail,Parent table id=5 Non-existent
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`db_test`.`c_table`, CONSTRAINT `c_table_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`))

-- Deleting or modifying records in parent tables with foreign key constraints will fail
update p_table set id = 5 where id = 1; -- fail,Parent table id=1 Existing foreign key constraints
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`db_test`.`c_table`, CONSTRAINT `c_table_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`))

delete  from p_table where id = 1;-- fail,Parent table id=1 Existing foreign key constraints
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`db_test`.`c_table`, CONSTRAINT `c_table_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`))


-- Delete the record of the child table first, and then delete the record of the parent table corresponding to the foreign key.
delete  from  c_table where id = 1;
select  *  from c_table ;-- 1 Bar record
2	Sub 22

delete  from p_table where id = 1;-- Success
select  *  from p_table ; -- 2 Bar record
2	Zhang 2
3	Zhang 3

------------Increase cascade operation------------- 
CREATE TABLE `c_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fkeyName` (`p_id`),
  CONSTRAINT `c_table_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

alter table c_table drop foreign key  c_table_ibfk_1 ; -- Delete foreign key constraints

CREATE TABLE `c_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fkeyName` (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

alter table c_table  -- Add foreign key with cascade operation
add constraint id_check
foreign key(p_id)
references p_table(id)
on delete cascade
on update cascade;

CREATE TABLE `c_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_check` (`p_id`),
  CONSTRAINT `id_check` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

delete from p_table ;  
delete  from c_table ;

insert into p_table(p_name) values('Zhang 1');
insert into p_table(p_name) values('Zhang 2');
insert into p_table(p_name) values('Zhang 3');
select  *  from p_table ; -- 3 Bar record
4	Zhang 1
5	Zhang 2
6	Zhang 3

insert into c_table(c_name,p_id) values('Sub 1',1); -- Failure, parent table does not exist id=1 Records
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`db_test`.`c_table`, CONSTRAINT `id_check` FOREIGN KEY (`p_id`) REFERENCES `p_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

insert into c_table(c_name,p_id) values('Sub 1',4);
insert into c_table(c_name,p_id) values('Sub 1',5);
select *  from  c_table
6	Sub 1	4
7	Sub 1	5

update p_table set id = 10 where id = 5;  -- Parent table id After modification, the parent table in the vocabulary id It will also be amended.
select  *  from p_table ; -- 3 Bar record
4	Zhang 1
6	Zhang 3
10	Zhang 2
select *  from  c_table;
6	Sub 1	4
7	Sub 1	10

delete  from p_table where id = 10;-- Delete the parent table id After recording, the corresponding word list id Records will also be deleted
select  *  from p_table ; 
4	Zhang 1
6	Zhang 3
select *  from  c_table;
6	Sub 1	4

The above is original.

I. Basic concepts

1. The definition of "key" and "index" in MySQL is the same, so foreign key and primary key are also a kind of index. The difference is that MySQL automatically indexes the primary keys of all tables, but foreign key fields must be indexed explicitly by users. Fields used for foreign key relationships must be indexed explicitly in all reference tables, and InnoDB cannot automatically create indexes.

2. Foreign keys can be one-to-one. Records of one table can only be connected with one record of another table, or one-to-many. Records of one table can be connected with multiple records of another table.

3. If you need better performance and do not need integrity checking, you can choose to use MyISAM table type. If you want to build tables in MySQL based on reference integrity and hope to maintain good performance on this basis, you'd better choose the table structure as innoDB type.

4. Use Conditions of Foreign Keys

The two tables must be InnoDB tables. MyISAM tables do not support foreign keys for the time being.

(2) Foreign key columns must be indexed. After MySQL 4.1.2, foreign key columns will be indexed automatically, but in earlier versions, they need to be established explicitly.

(3) The columns of the two tables with foreign key relationship must be similar in data type, that is, columns that can convert to each other, such as int and tinyint, but int and char can't.

5. Benefits of foreign key: It can associate two tables, ensure data consistency and realize some cascading operations.

II. Usage

1. The grammar of creating foreign keys:

Definition grammar of foreign keys:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

This grammar can be used in CREATE TABLE and ALTER TABLE. If CONSTRAINT symbol is not specified, MYSQL automatically generates a name.

ON DELETE and ON UPDATE indicate event trigger limits and can set parameters:

RESTRICT

(2) CASCADE (following foreign key changes)

(3) SET NULL (set null value)

SET DEFAULT (default value)

NO ACTION (no action, default)