(MYSQL) Setting foreign keys for a database

There are two ways to set a foreign key, one to associate a foreign key when creating a table, and another to add a foreign key to a table that already exists. Here's how:

1. Actions when creating a table

Class table (Main Table):

CREATE TABLE TB1 (

ID INT PRIMARY KEY auto_increment,

ClassName VARCHAR () not NULL

）;

User table (child table):

CREATE TABLE TB2 (

ID INT PRIMARY KEY auto_increment,

Username VARCHAR () not NULL,

ClassID INT,

FOREIGN KEY (ClassID) REFERENCES tb1 (ID) on DELETE CASCADE on UPDATE CASCADE

);

----------------------------------------------------------------------------

2. Adding foreign keys on the basis of existing tables

ALTER TABLE tb3 ADD CONSTRAINT t_c FOREIGN KEY (classid) REFERENCES tb1 (ID);

----------------------------------------------------------------------------

You can then use the command to view the CREATE table operation, show create TABLE TB2;

PRIMARY KEY (' id '),

KEY ' ClassID ' (' ClassID '),

CONSTRAINT ' Tb2_ibfk_1 ' FOREIGN KEY (' classid ') REFERENCES ' tb1 ' (' id ') on DELETE CASCADE

Indicates that the foreign key has been successfully created.

-----------------------------------------------------------------------------

Additional notes:

CASCADE: Deletes or updates from the parent table and automatically deletes or updates the matching rows in the child table.

Set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. The precondition is to ensure that the foreign key column is not a not NULL.

RESTRICT: denies deletion or update of the parent table.

This article is from the "W1SW" blog, make sure to keep this source http://cfdeng.blog.51cto.com/9873532/1871166

(MYSQL) Setting foreign keys for a database