Cheng Plan--mysql foreign key

Defined

FOREIGN key: If a field of one table points to the primary key of another table, it is called a foreign key. The table that is pointed to, called the main table, also called the parent table, then the other table is from the table, also called the child table

#先新建两个表

 mysql>  create  table   author_table ( ->  author_id int  (4 ) not  null  primary  key   auto_increment,  ->  author_name char  (20 ) not  null  Span style= "color: #000000"); Query OK,  0  rows affected (0.02  sec) 

Mysql> Create Tablearticle_table ( -article_idint(4) not NULL Primary KeyAuto_increment, -Article_titleChar( -) not NULL,     -author_idint(4) not NULL,     - Foreign Key(author_id)Referencesauthor_table (author_id)); #这一步使得article_table中的author_id字段成为外键Query OK,0Rows Affected (0.02Sec

#添加数据

Mysql> Insert  intoAuthor_tableValues     -(1,'Zhao'),     -(2,'Qian'),     -(3,'Sun'),     -(4,'Li'); Query OK,4Rows Affected (0.01sec) Records:4Duplicates:0Warnings:0

Mysql> Insert  intoArticle_tableValues     -(1001,'C + +',1),     -(1002,'Java',1),     -(1003,'python',2),     -(1004,'MySQL',3),     -(1005,'Jacascript',4); Query OK,5Rows Affected (0.00sec) Records:5Duplicates:0Warnings:0

#子表和附表之间的约束

0 article_table can not add author_id to 5 records;

1 author_table cannot delete author_id 4 records because there are also author 4 articles in the child table

#删除外键约束

MySQL>altertabledropforeignkey0 rows Affected (0.020  0  0

#级联操作

#添加外键

Mysql> Alter Tablearticle_table - Add Foreign Keyfk_id (author_id) #这里fk_id可以自己指定 - Referencesauthor_table (author_id) -  on Delete Cascade #cascade表示关联操作. The child table relies on the data association in the parent table to delete or update     -  on Update Cascade; Query OK,5Rows Affected (0.02sec) Records:5Duplicates:0Warnings:0

#set NULL keyword

Set NULL, which indicates that the child table data does not point to the parent table for any records. When NULL and cascade are not set, the default is restrict (the related action of rejecting the primary table), so the start of the primary table cannot delete the data

Cheng Plan--mysql foreign key