The "MySQL" Create table and foreign key Delete table are in order.

1. The following is a table statement that is exported directly from the database.

1-- ----------------------------2--Table structure forFiles3-- ----------------------------4 DROP TABLE IF EXISTS ' files ';5 CREATE TABLE ' files ' (6' ID 'int(11) not NULL,7' FileName ' varchar (50) DEFAULT NULL,8' FilePath ' varchar (200) DEFAULT NULL,9 ' updatedate ' datetime DEFAULT NULL,Ten' UserId 'int(11) DEFAULT NULL, One PRIMARY KEY (' id ') A) Engine=innodb DEFAULT charset=UTF8; -  --- ---------------------------- the--Table structure forUsers --- ---------------------------- - DROP TABLE IF EXISTS ' users '; - CREATE TABLE ' users ' ( +' ID 'int(11) not NULL auto_increment, -' Name ' varchar (30) DEFAULT NULL, +' Email ' varchar (50) DEFAULT NULL, A' Age 'int(11) DEFAULT NULL, at' Phone ' varchar (20) DEFAULT NULL, - PRIMARY KEY (' id ') -) Engine=innodb auto_increment=7 DEFAULT Charset=utf8;

2. We establish a relationship between the tables files and the users, add the foreign key FILES_USERS_FK to the table files, and build the index on the FILES_USERS_FK.

1-- ----------------------------2--Table structure forFiles3-- ----------------------------4 DROP TABLE IF EXISTS ' files ';5 CREATE TABLE ' files ' (6' ID 'int(11) not NULL,7' user_id 'int(11) NULL,8' FileName ' varchar (50) DEFAULT NULL,9' FilePath ' varchar (200) DEFAULT NULL,Ten ' updatedate ' datetime DEFAULT NULL, One' UserId 'int(11) DEFAULT NULL, A PRIMARY KEY (' id '), - KEY ' files_users_fk ' (' user_id ') -) Engine=innodb DEFAULT charset=UTF8; the  --- ---------------------------- ---Table structure forUsers --- ---------------------------- + DROP TABLE IF EXISTS ' users '; - CREATE TABLE ' users ' ( +' ID 'int(11) not NULL auto_increment, A' Name ' varchar (30) DEFAULT NULL, at' Email ' varchar (50) DEFAULT NULL, -' Age 'int(11) DEFAULT NULL, -' Phone ' varchar (20) DEFAULT NULL, - PRIMARY KEY (' id ') -) Engine=innodb auto_increment=7 DEFAULT charset=UTF8; -  inALTER TABLE files ADD CONSTRAINT files_users_fk FOREIGN KEY (' user_id ') REFERENCES users (' id ');

You can see that the files depend on the table users and the dependent tables cannot be deleted first. That is, you need to delete the current table before deleting the Foreign Key association table

Let's take a look at the concept:

Look at the on Delete property first, possibly the value: No Action, Cascade,set Null, restrict property.

When the value is no action or restrict, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key and, if there is one, it is not allowed to be deleted.

When the value is cascade, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key, and if there is one, the record in the child table (that is, the table containing the foreign key) is also deleted.

When the value is set NULL, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so, set the foreign key value in the child table to null (this requires that the foreign key is allowed null).

On update is the same?

When the value is no action or restrict, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key and, if there is one, the update is not allowed.

When the value is cascade, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key and, if there is one, update the record in the child table (that is, the table that contains the foreign key).

When the value is set NULL, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key and, if there is one, set the foreign key value in the child table to null (but this requires that the foreign key is allowed to take null).

Like what:

ALTER TABLE files ADD CONSTRAINT files_users_fk FOREIGN KEY (' user_id ') REFERENCES users (' ID ') on DELETE SET NULL on UPDATE CASCADE;

--

The "MySQL" Create table and foreign key Delete table are in order.