Detailed graphic and text explanation of MySQL learning foreign keys

1. For example, there are two tables "category table" and "product table". In order to indicate which category the product belongs to, usually we will add a column to the product table to store the classification cid information. This column is called : foreign key.

At this time, the classification table category is called the main table, and cid is called the primary key; the product table products is called the slave table, and category_id is called the foreign key.

Features of foreign keys: 1) The value of the foreign key of the secondary table is a reference to the primary key of the primary table

　　2) The type of the foreign key of the secondary table must be consistent with the primary key of the primary table.

The purpose of using foreign keys: to ensure data integrity

2. First create a database web09 in the command prompt, and create two tables category and product. The statements are as follows:

Insert multiple pieces of data respectively

eg.insert into category(cid,cname) values('c001','家电');
insert into product(pid,pname,price,category_id) values('p001','联想','5000','c001');

is displayed as follows:

3. Declare foreign key constraints

Syntax: alter table from table add [constraint] [foreign key name] foreign key (from table foreign key field name) references main table (primary key of main table);

[Foreign key name] is used to delete foreign key constraints. It is generally recommended to end with "_fk"

After the constraint is declared, if I want to delete the category table The data of cid=c003 cannot be deleted

#4. Release the constraint

Syntax: alter table drop foreign key from the table Foreign key name

Continue to delete the data with cid=c003 in the category table. You can delete it

Note: Foreign keys from the slave table cannot be added to the main table. Existing records

The master table cannot delete records that have been referenced in the slave table.

5. One-to-many table creation principle:

6. Many-to-many table creation principle:

The above is the detailed content of Detailed graphic and text explanation of MySQL learning foreign keys. For more information, please follow other related articles on the PHP Chinese website!