Database-FOREIGN KEY constraints

1. Constraints

Constraints guarantee the integrity and consistency of the data.

Constraints are table-level constraints and column-level constraints, which are called column-level constraints for a field, and are called table-level constraints for two and more than two constraints.

3. Constraint types include

Not NULL (non-null constraint)

PRIMARY key (primary KEY constraint)

Unique KEY (single constraint)

Default KEY (Defaults constraint)

FOREIGN key (FOREIGN KEY constraint)

First, let me introduce the requirements for foreign key constraints.

1. Both the parent and child tables must use the same storage engine (Znnodb must be used). And the use of temporary tables is prohibited.

2. The data tables and reference tables must have similar data types. The length of the number or whether the sign bit must be the same, but if it is a character, the length can be different.

3. Foreign key columns and reference columns must be indexed, and MySQL will automatically create indexes if the foreign key columns are not indexed.

Here we insert a sentence, how to edit the data table of the storage engine?

In the MySQL configuration file

Default-storage-engine=innodb;

Example:

Parent table:

CREATE TABLE Provinces (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

PName VARCHAR () not NULL

)

Child table:

CREATE TABLE Users (

ID SMALL UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR () not NULL;

PID SMALLINT UNSIGNED,

FOREIGN (PID) referrence provinces (ID);

)

Referential actions for FOREIGN KEY constraints

1 CASCADE: Delete or update from parent table and automatically delete or update matching rows in child table

2.SET null: Delete or update rows from the parent table, and set the foreign key column in the child table to null, if this option is used, you must ensure that the child table column does not specify not NULL

3. Resirict: Reject deletion of parent table or update operation

4.NOT NULL: Standard SQL keyword, same as resirct in MySQL.

One more example.

Parent table:

CREATE TABLE Provinces (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

PName VARCHAR () not NULL

)

Child table:

CREATE TABLE Users (

ID SMALL UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR () not NULL;

PID SMALLINT UNSIGNED,

FOREIGN (PID) referrence provinces (id) on DELETE CASCADE;

)

If it is possible to delete the ID from the parent table, when the corresponding foreign key is established, the two tables must be interpolated to complete the parent table before the child table can be completed, see the following actions:

INSERT provinces (pname) VALUES ("A").

INSERT provinces (pname) VALUES ("B")

INSERT provinces (pname) VALUES ("C")

INSERT users (Username,pid) VALUES ("TOM", 3);

INSERT users (Username,pid) VALUES ("John", 2);

Cannot insert a number other than (provinces) because the ID value in the

If delete * from provinces WHERE id=3;

Then the users in the pid=3 also deleted.

Database-FOREIGN KEY constraints