MySQL foreign key constraints

[CONSTRAINTsymbol] FOREIGNKEY [id] (index_col_name ,...) REFERENCEStbl_name (index_col_name ,...) [ONDELETE {RESTRICT | CASCADE | SETNULL | NOACTION | SETDEFAULT}] [ONUPDATE {RESTRICT | CASCADE | SETNULL | NOACTION | SETDEFAULT}

[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}

[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}]

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

MySQL 3.23.44 and later versions support foreign key constraints for InnoDB Engine tables.
Usage conditions of foreign keys:
1. The two tables must be InnoDB tables, and MyISAM tables do not currently support foreign keys (it is said that foreign keys may be supported in later versions, but at least not supported currently );
2. The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating the foreign key. However, if the foreign key column is in an earlier version, the index must be created;
3. the columns of the foreign key relationship must be of similar data types, that is, columns that can be converted to each other. For example, int and tinyint can be used, but int and char cannot;


Benefits of Foreign keys: two tables can be associated to ensure data consistency and perform cascade operations;


Syntax for defining 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 syntax can be used in create table and alter table. If the CONSTRAINT symbol is not specified, MYSQL automatically generates a name.
On delete and on update indicate the event trigger limit. You can set the following parameters:
RESTRICT)
CASCADE (with foreign key changes)
Set null (set null)
SET DEFAULT)
No action (no action, default)






Add a foreign key to the added data table:
Syntax: alter table name add constraint FK_ID foreign key (your foreign key field name) REFERENCES External table name (corresponding table's primary key field name );
Example: alter table tb_active add constraint FK_ID foreign key (user_id) REFERENCES tb_user (id)
// FK_ID is the name of the foreign key
/*
Create table 'tb _ active '(
'Id' int (11) not null AUTO_INCREMENT,
'Title' varchar (100) character set utf8 COLLATE utf8_unicode_ci not null,
'Content' text character set utf8 COLLATE utf8_unicode_ci not null,
'User _ id' int (11) not null,
Primary key ('id '),
KEY 'user _ id' ('user _ id '),
KEY 'user _ id_2 '('user _ id '),
CONSTRAINT 'fk _ id' foreign key ('user _ id') REFERENCES 'tb _ user' ('id ')
) ENGINE = InnoDB default charset = latin1
*/


Delete foreign key
Syntax: alter table table-name drop foreign key key-id;
For example, alter table 'tb _ active' drop foreign key 'fk _ id'



Automatic Key Update and deletion:
The foreign key can ensure the integrity of the newly inserted record. However, what if the deleted record is named in the REFERENCES clause? What happens in a secondary table that uses the same value as a foreign key?

Obviously, those records should also be deleted, otherwise there will be a lot of meaningless isolated records in the database, MYSQL can forward to foreign key... the REFERENCES modifier adds an on delete or on update clause to simplify the task. It tells the database how to handle isolated tasks in this case.

Keyword meaning
CASCADE deletes all records that have reference relationships with deleted key values.
Set null: modify all records that have reference relationships with the deleted key value and replace them with NULL values (only for Fields marked as not null)
RESTRICT rejects the deletion request until the secondary table that uses the deleted key value is manually deleted without reference (this is the default setting and the safest setting)
No action, NO ACTION

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,
For example, if a series of tables are connected through a foreign key relationship and the on delete cascade rule, any changes to the primary table will lead to the deletion of some records that will be associated with the original deletion without warning. Therefore, we need to check these rules before the operation, check again after the operation.



Add foreign key
Alter table locstock add foreign key locstock_ibfk2 (stockid) references product (stockid)
Locstock is the table name, locstock_ibfk2 is the name of the foreign key column in the first brackets of the foreign key name, product is the table name, and the second column is the name of the column associated with the foreign key.

Delete foreign key
Alter table locstock drop foreign key locstock_ibfk2

View the table's Foreign keys
Show create table locstock

[CONSTRAINT symbol] foreign key [id] (index_col_name ,...)
REFERENCES tbl_name (index_col_name ,...)
[On delete {RESTRICT | CASCADE | set null | no action}]
[On update {RESTRICT | CASCADE | set null | no action}]


All tables must be of the InnoDB type and cannot be temporary tables.
· In the referenced table, an index is required, and the foreign key column is listed in the same order as the first column. If such an index does not exist, it must be automatically created in the reference table.
· In the referenced table, there must be an index. The referenced columns are listed in the same order as the first column.
· The index prefix of foreign key columns is not supported. One of the consequences is that BLOB and TEXT columns are not included in an external key, because the index of these columns must always contain a prefix length.
· If CONSTRAINTsymbol is given, it must be unique in the database. If it is not given, InnoDB automatically creates this name.