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

This article shows you how to add foreign keys in mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Mysql add Foreign key

RESTRICT: as long as there is data in this table that points to the main table, the relevant records cannot be deleted in the main table.

Generally speaking, restrict is the safest, and of course, depending on the specific business, cascade is very cool, but the data is gone.

CASCADE: if you delete a record in the table that foreign key points to, all records in this table like that key will be deleted together.

Add a foreign key to the book table:

Explicitly specify the name of the foreign key: alter table book add constraint FK_BOOK foreign key (pubid) references pub_com (id) on delete restrict on update restrict

If you do not specify a foreign key name, mysql will automatically create a foreign key name for you: alter table book add foreign key (pubid) references pub_com (id) on delete restrict on update restrict

Use: show create table book; command to view

Use: desc book; to view the table structure

#

Note that the above article is a reference to network resources. However, I think this article basically explains what is related to foreign key constraints.

It's just that there are some places that are not perfect.

Such as to the following situation.

-- restrict exported tables

--

--

-- limit table `room_ staus`

--

ALTER TABLE `room_ staus` ADD CONSTRAINT `fk_rooms_ roomtype` FOREIGN KEY (`room_ id`, `room_ id`) REFERENCES `room_ type` (`hotel_ id`, `room_ id`) ON DELETE CASCADE ON UPDATE CASCADE

Alter table child add constraint foreign key (id)

References parent (id)

Define the data table

If a computer manufacturer keeps product information about the whole machine and accessories. The table used to store the product information of the whole machine is called Pc;. The table used to store the supply information of accessories is called Parts.

There is a field in the Pc table that describes the CPU model used on this computer

There is a corresponding field in the Parts table that describes the model of CPU, which we can think of as a list of all CPU models.

Obviously, the CPU used by the computer produced by this factory must be the model that exists in the supply information table (parts). At this point, there is a constraint relationship between the two tables (constraint)-the CPU model in the Pc table is constrained by the model in the Parts table.

First, let's create the parts table:

CREATE TABLE parts (

... Field definition.

Model VARCHAR (20) NOT NULL

... Field definition.

);

Next is the Pc table:

CREATE TABLE pc (

... Field definition.

Cpumodel VARCHAR (20) NOT NULL

... Field definition.

}

Set the index

To set a foreign key, both fields in the reference table (referencing table, that is, Pc table) and the referenced table (referenced table, that is, parts table) must be indexed (index).

For the Parts table:

ALTER TABLE parts ADD INDEX idx_model (model)

This means to add an index to the parts table, which is based on the model field, and give the name to the idx_model.

The same is true for Pc tables:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel)

In fact, these two indexes can be set when the table is created. This is just to highlight its necessity.

Define foreign key

The following is to establish the kind of "constraint" described earlier between the two tables. Because the CPU model of pc must refer to the corresponding model in the parts table, we set the cpumodel field of the Pc table to "FOREIGN KEY", that is, the reference value of this key comes from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model

FOREIGN KEY (cpumodel)

REFERENCES parts (model)

The first row says to set a foreign key for the Pc table, and the second row means to set the cpumodel field of this table to the foreign key; the third row says that the foreign key is constrained by the model field of the Parts table.

In this way, our foreign keys will be fine. If we try to CREATE a Pc, which uses a CPU model that does not exist in the Parts table, then MySQL will prevent the PC from being CREATE.

Cascade operation

Consider the following situation:

The technician found that the models of a series of cpu (there may be many) entered into the parts table a month ago all typed a wrong letter and now need to be corrected. What we hope is that when those Referenced Column in the parts table change, the Referencing Column in the corresponding table will also be automatically corrected.

When defining a foreign key, you can add a keyword like this at the end:

ON UPDATE CASCADE; means that when the main table is updated, the child table (s) produces a cascading update action, which some people like to call a "cascade" operation. :)

If you write this sentence in its entirety, it is:

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model

FOREIGN KEY (cpumodel)

REFERENCES parts (model)

ON UPDATE CASCADE

In addition to CASCADE, there are also operations such as RESTRICT (forbidding changes to the main table), SET NULL (the corresponding fields of the child table are set to empty), and so on.

The above is how to add foreign keys to mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.