Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use foreign keys and three paradigms for data tables in the Mysql database. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Data table optimization

Optimize the commodity information table

1. Create a table of product categories:

Create table if not exists goods_cates (id int unsigned primary key auto_increment, name varchar (40) not null)

two。 Write the category of goods into the list of categories of goods:

Note: the query result of inserting another table does not need to be added values

Insert into goods_cates (name) (select cate_name from goods group by cate_name)

3. Update the commodity category table to the commodity table:

Connect the commodity table with the category table and use the category table id instead of the category content of the commodity table

Update (goods_cates inner join goods on goods_cates.name=goods.cate_name) set goods.cate_name=goods_cates.id

4. Modify the table structure:

Rename the field name cate_ name of commodity list to cate_id

Alter table goods change cate_name cate_id int unsigned not null

Foreign key

1. Use of foreign keys:

Add a foreign key constraint and insert a data line to determine whether the cate_id exists in the id in goods_cates. If it does not exist, the data insertion fails.

Alter table Table name 1 add foreign key (Field name 1) references Table name 2 (Field name 2)

(illegal data entry can be prevented after setting foreign keys)

Foreign key (field name 1) references table name 2 (field name 2)

(you can also set a foreign key directly when creating a table, as long as the table to be joined exists.)

two。 Disadvantages of foreign keys:

Although foreign keys can effectively prevent illegal data entry, it will greatly reduce the update efficiency of the table, so in actual development, foreign key constraints are generally not applicable, and the legitimacy of data can be judged at the transaction level.

3. Deletion of foreign keys:

Displays the statement that creates the table

Show create table goods

Delete the foreign key after obtaining the foreign key name:

Alter table goods drop foreign key goods_ibfk_1

Three paradigms

1. First normal form (1NF): emphasizes the atomicity of columns, that is, columns can no longer be divided into other columns.

two。 Second normal form (2NF): based on 1NF, a table must have a primary key, and non-primary key fields must be completely dependent on the primary key, not just a portion of the primary key.

3. The third normal form (3NF): based on 2NF, non-primary key columns must be directly dependent on the primary key, and there can be no transitive dependency.

The database in line with the third paradigm is a more reasonable database.

On how to use foreign keys and three paradigms in the Mysql database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.