Add a foreign key to mysql

Mysql adds a foreign key RESTRICT: as long as the table contains data directed to the master table, the relevant records cannot be deleted in the master table. In general, restrict is the safest option. Of course, it depends on the specific business. cascade is cool, but the data Shard is gone. CASCADE: If a record is deleted from the table pointed to by the foreign key, all records in the table with the same key will be deleted together. Add a foreign key to the book table: 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 no foreign key name is specified, 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: run the show create table book command to view and use desc book. You can view the table structure #########. The preceding articles refer to network resources. However, I think this article gives a clear explanation of what is related to foreign key constraints. However, some of them are not perfect. For example, in the following situation. -- Restrict the exported TABLE -- restrict the TABLE 'room _ staus' -- alter table 'room _ staus' add constraint 'fk _ rooms_roomtype 'foreign KEY ('Hotel _ 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 a data table. If a computer manufacturer, its database stores the product information of the machine and accessories. The table used to save the product information of the entire machine is called Pc; the table used to save the accessory supply information is called Parts. In the Pc table, there is a field used to describe the CPU model used by the computer. In the Parts table, there is a field describing the CPU model, we can think of it as a list of all CPU models. Obviously, the CPU used by the computer produced by this manufacturer must be the model existing in the supply information table (parts. In this case, two tables have a constraint. The CPU model in the Pc table is subject to the Model constraints in the Parts table. First, CREATE a 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 ...}; to set the index, if you want to set a foreign key, in the reference table (referencing table, that is, the Pc table) and the referenced table (referenced table, that is, the parts table, indexes must be set for both fields ). For Parts tables: alter table parts add index idx_model (model); this statement means that an INDEX is added to the parts TABLE and the INDEX is created on the model field, name the index idx_model. Similar to Pc tables: alter table pc add index idx_cpumodel (cpumodel); in fact, these two indexes can be set during TABLE creation. This is just to highlight its necessity. Define the "constraint" described above for the two tables under the foreign key ". Because the CPU model of the pc must refer to the corresponding model in the parts table, we set the cpumodel field of the Pc table to "foreign key" (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 line is to set a foreign key for the Pc TABLE and name it fk_cpu_model; the second row sets the cpumodel field of the table as a foreign key. The third row indicates that the foreign key is restricted by the model field of the Parts table. In this way, we can use the foreign key. If we try to CREATE a Pc, and the CPU model used does not exist in the Parts table, MySQL will disable this PC from being created. Cascade operations consider the following situation: technicians found that all models of a series of CPUs (which may have many models) entered into the parts table a month ago all entered a wrong letter, now you need to correct it. We hope that when the Referenced columns in the parts table change, the Referencing Column in the corresponding table can also be automatically corrected. When defining foreign keys, you can add the keyword "on update cascade" at the end. That is, when the primary table is updated, the sub-tables (WHO) generate a chain UPDATE action, it seems that some people like to call this cascade operation. :) If the statement is completely written, it is: alter table pc add constraint fk_cpu_model foreign key (cpumodel) REFERENCES parts (model) on update cascade; besides CASCADE, you can also perform operations such as RESTRICT (change of the master table is prohibited) and set null (the corresponding field of the sub-table is SET to NULL. Author: My friends