Setting foreign key attributes of databases

Foreign key:

Data consistency and integrity are maintained to control data stored in the foreign key table. Associate two tables. The foreign key can only reference the values of columns in the External table!
For example:
A and B
Table A contains the customer ID and customer name.
Orders of each customer exist in table B.
With the foreign key, you can only delete the Customer X in Table A after you confirm that there is no Customer X order in table B.

Prerequisites for creating a foreign key: the columns in the table must be of the same type as the foreign key (the foreign key must be the external primary key ).

Specify the primary key Keyword: foreign key (column name)

Reference foreign key Keyword: References <foreign key table name> (foreign key column name)

Event trigger restrictions: On Delete and on update. You can set the parameters cascade, restrict, and set null ), set default (Set default value), [Default] no action

The attributes in on Delete are described as follows:

On Delete {No Action | cascade | set null | set default}
Specifies the actions to take if the rows in the created table have reference relationships and the referenced rows have been deleted from the parent table. The default value is no action.

No action
The database engine will cause an error and roll back the row deletion operation on the parent table.

Cascade
If a row is deleted from the parent table, the corresponding row is deleted from the reference table.

Set null
If the row corresponding to the parent table is deleted, all values that constitute the foreign key are set to null. To execute this constraint, the foreign key column must be null.

Set Default
If the row corresponding to the parent table is deleted, all values that constitute the foreign key are set to the default value. To execute this constraint, all foreign key columns must be defined by default. If a column can be null and no explicit default value is set, null is used as the implicit default value for the column.

For example:

Outtable table primary key ID type int

Create a table with foreign keys:

Create Table temp (
Id int,
Name char (20 ),
Foreign key (ID) References outtable
(ID) on Delete cascade on update cascade );
Note: set the ID column as the foreign key reference External table ID column as the foreign key value Delete this table corresponding to the column sieve when the foreign key value changes the corresponding column value in this table.

 

Refer:

Http://database.51cto.com/art/201011/234188.htm

Http://zhidao.baidu.com/question/302718741.html