Mysql-InnoDB storage engine Foreign key constraint (ForeignKeyConstraint) _ MySQL

Mysql-InnoDB storage engine Foreign key constraint -- (ForeignKeyConstraint) Mysql foreign key

SQL foreign key constraints

The foreign key in one table points to the primary key in another table.

The foreign key constraint is used to prevent the action of damaging the join between tables.

The foreign key constraint can also prevent illegal data insertion into the foreign key column because it must be one of the values in the table to which it points.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL statement creates a FOREIGN KEY for the "Id_P" column when creating the "Orders" table:

MySQL:

CREATE TABLE Orders(Id_O int NOT NULL,OrderNo int NOT NULL,Id_P int,PRIMARY KEY (Id_O),FOREIGN KEY (Id_P) REFERENCES Persons(Id_P))

SQL Server/Oracle/MS Access:

CREATE TABLE Orders(Id_O int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,Id_P int FOREIGN KEY REFERENCES Persons(Id_P))

To name the foreign key constraint and define the foreign key constraint for multiple columns, use the following SQL syntax:

For MySQL/SQL Server/Oracle/MS Access:

CREATE TABLE Orders(Id_O int NOT NULL,OrderNo int NOT NULL,Id_P int,PRIMARY KEY (Id_O),CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P))

SQL FOREIGN KEY Constraint on ALTER TABLE

If the "Orders" table already exists, use the following SQL statement to create a FOREIGN KEY constraint for the "Id_P" column:

For MySQL/SQL Server/Oracle/MS Access:

ALTER TABLE OrdersADD FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

To name the foreign key constraint and define the foreign key constraint for multiple columns, use the following SQL syntax:

For MySQL/SQL Server/Oracle/MS Access:

ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

Revoke the foreign key constraint

To revoke the foreign key constraint, use the following SQL statement:

MySQL:

ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders

SQL Server/Oracle/MS Access:

ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders

Foreign key constraints of InnoDB

The syntax for defining foreign key constraints in InnoDB looks as follows:

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

InnoDB rejects any INSERT or UPDATE operation that tries to create a foreign key value in the sub-table but does not match the candidate key value in the parent table. A parent table has some child tables with matched rows. InnoDB performs some actions on any UPDATE or DELETE operations that attempt to UPDATE or DELETE the candidate key values in the parent table, this action depends ON the referential action specified by the on update and on detete clauses of the foreign key clause. When a user tries to delete or update a row from a parent table and has one or more matched rows in the child table, InnoDB has five options based on the action to be taken:

• CASCADE:Deletes or updates matched rows from the parent table and automatically deletes or updates the child table. On delete cascade and on update cascade are both available.Between two tables, you should not define several on update cascade clauses that take actions in the same column of the parent or child table.

• Set null:Delete or update rows from the parent table, and set the foreign key column in the child table to NULL.. If the foreign key column does NOT specify the not null qualifier, this is unique and valid. The on delete set null and on update set null clauses are supported.

• No action: in ANSI SQL-92 standards,No action indicates that this ACTION is not taken. if a related foreign key value is in the referenced table, attempts to delete or update the primary key value are not allowed. InnoDB rejects the deletion or update operation on the parent table..

• RESTRICT:The deletion or update of the parent table is denied.. No action and RESTRICT are both the same. DELETE the on delete or on update clause. (Some database systems have an extension check, and no action is an extension check. In MySQL, foreign key constraints are checked immediately, so no action and RESTRICT are the same ).

• Set default: This action is recognized by the parser, but InnoDB rejects table definitions that contain the on delete set default or on update set default clause.

A simple example of connecting a parent table and a child table with a single column foreign key is as follows:

CREATE TABLE parent(id INT NOT NULL,       PRIMARY KEY (id)) TYPE=INNODB;CREATE TABLE child(id INT, parent_id INT,       INDEX par_ind (parent_id),       FOREIGN KEY (parent_id) REFERENCES parent(id)       ON DELETE CASCADE) TYPE=INNODB;

InnoDB allows you to use alter table to add a new foreign key constraint to a TABLE:

ALTER TABLE yourtablename    ADD [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}]

Remember to create the required index first. You can also use alter table to add a self-referenced foreign key constraint to a TABLE.

InnoDB also supports the use of alter table to remove foreign keys:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;