MySQL Learning (4) -- foreign key, mysql learning foreign key

MySQL Learning (4) -- foreign key, mysql learning foreign key

1. For example, there are two tables: "Classification Table" and "commodity table". To indicate the category of a commodity, we usually add a column to the commodity table, used to store information about the cid type. This column is called a foreign key.

In this case, the category table category is called the primary table, and the cid is called the primary key. The product table products is called the slave table, and the category_id is called the foreign key.

Foreign key features: 1) the foreign key value from the table is a reference to the primary key of the primary table.

2) The foreign key type of the slave table must be the same as the primary key of the master table.

Purpose: To ensure data integrity

2. Create a database web09 in the command prompt and create two tables, category and product. The statement is as follows:

Insert multiple data records separately

Eg. insert into category (cid, cname) values ('c001', 'appliance ');

Insert into product (pid, pname, price, category_id) values ('p001', 'lenovo ', '000000', 'c001 ');

Shown as follows:

3. Declare foreign key constraints

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

[Foreign key name] used to delete foreign key constraints. It is generally recommended that the end of "_ fk"

If I want to delete the data with cid = c003 in the category table after the constraint declaration, it cannot be deleted.

4. Release Constraints

Syntax: alter table drop foreign key name from the table

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

Note: you cannot add records that do not exist in the master table from the foreign key.

The primary table cannot delete records that have been referenced from the table.

5. One-to-multiple table Creation Principles:

6. Multi-to-Multi-Table Creation Principles: