MySQL Learning (four)--foreign key

1, for example, there are two tables "classification table" and "Commodity table", in order to indicate which category the product belongs to, usually we will add a column on the product table, for the classification of CID information, this column is called: Foreign key.

At this time the Category table category is called the Main table, the CID is called the primary key, the commodity table products are called from the table, category_id called the foreign key.

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

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

Purpose of using foreign keys: ensure data integrity

2, first at the command prompt characters create a database web09, and create two tables category and product, the statement is as follows:

Insert multiple data in separate sections

Eg.insert into category (Cid,cname) VALUES (' c001 ', ' Jiadian ');

Insert into product (pid,pname,price,category_id) VALUES (' P001 ', ' Lenovo ', ' ' ', ' c001 ');

Shown below:

3. Declaring 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 primary table);

[Foreign key name] is used to delete foreign key constraints, the general recommendation "_FK" end

After the constraint declaration, if I want to delete the cid=c003 data in the category table, I can't delete

4. Release constraints

Syntax: ALTER TABLE drop FOREIGN key foreign key name from table

Continue to delete the cid=c003 data in the category table, you can delete

Note: You cannot add records that do not exist in the primary table from the Foreign key table

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

5, one-to-many table principle:

6, many-to-many building table principle:

MySQL Learning (four)--foreign key