Shulou(Shulou.com)06/01 Report--

This article will explain in detail the method of setting foreign key constraints in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The method of setting the foreign key constraint in mysql: you can specify the foreign key through the FOREIGN KEY keyword, syntax "ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (column name) REFERENCES main table name (column name)".

(1) the use of foreign keys:

There are two main functions of foreign keys:

One is to let the database ensure the integrity and consistency of the data through foreign keys.

One is to increase the readability of ER diagrams.

Some people think that the establishment of foreign keys will bring great trouble to the operation of the database during development. Because the database sometimes fails to pass the foreign key detection and causes the developer to delete, the insert operation fails. They think it's troublesome.

In fact, this official foreign key forces you to ensure the integrity and consistency of your data. This is a good thing.

For example:

There is a basic data table that records all the information about the goods. All other tables store merchandise ID. When querying, you need to connect a table to query the name of the product. There is a commodity ID field in the merchandise table of document 1 and a commodity ID field in the merchandise table of document 2. If you do not use foreign keys, when documents 1 ID=3 2 all use the goods of goods, if you delete the corresponding record of ID=3 in the goods table, you will not find the name of the goods when you look at the documents 1 field 2.

When there are few tables, some people think that when the program is implemented, it is possible to ensure the integrity and consistency of the data by writing scripts. That is, in the operation of deleting the item, it is necessary to check whether the item whose ID is 3 has been used in the document 1J2. But when you finish writing the script, the system has added a document 3, which also saves the item ID to find a field. If you do not use foreign keys, you will still be unable to find the product name. You can't go back to modify your script to check if the item is being used every time you add a document that uses the item's ID field. At the same time, the introduction of foreign keys will slow down speed and performance.

(2) add the format of foreign key:

ALTER TABLE yourtablename

ADD [CONSTRAINT foreign key name] FOREIGN KEY [id] (index_col_name,...)

REFERENCES tbl_name (index_col_name,...)

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Description:

On delete/on update, used to define delete,update operations. The following are the various types of constraints for update,delete operations:

CASCADE:

The foreign key table foreign key field value will be updated, or the column will be deleted.

RESTRICT:

RESTRICT is also the equivalent of no action, that is, nothing is done. That is, reject the parent table update foreign key associated column, delete record.

Set null:

When the foreign key associated field of the parent face is update, delete, the foreign key column of the child table is set to null.

For insert, the value entered by the foreign key column of the child table can only be the existing value of the foreign key associated column of the parent table. Otherwise, something will go wrong.

The definition of foreign key is subject to the following conditions: (prerequisite)

1)

All tables must be InnoDB type, they cannot be temporary tables. Because in MySQL, only tables of type InnoDB support foreign keys.

2)

All fields that want to create foreign keys must be indexed.

3)

For non-InnoDB tables, the FOREIGN KEY clause is ignored.

Note:

When creating a foreign key, you cannot add quotation marks when defining the foreign key name.

For example, constraint 'fk_1' or constraint "fk_1" is wrong

(3) View foreign keys:

SHOW CREATE TABLE * *; you can view the code of the newly created table and its storage engine. You can also see the settings of the foreign key.

Delete the foreign key:

Alter table drop foreign key 'foreign key name'.

Note:

Only when defining a foreign key, use the constraint foreign key name foreign key. It is convenient to delete foreign keys.

If you do not define it, you can:

First enter: alter table drop foreign key-- > will prompt an error. In the error message, the default foreign key name of foreign key is displayed.->

Use it to delete foreign keys.

(4) examples

Example 1:

4.1

CREATE TABLE parent (id INT NOT NULL

PRIMARY KEY (id)

) TYPE=INNODB;-- type=innodb is equivalent to engine=innodb

CREATE TABLE child (id INT, parent_id INT

INDEX par_ind (parent_id)

FOREIGN KEY (parent_id) REFERENCES parent (id)

ON DELETE CASCADE

) TYPE=INNODB

After inserting data into parent, insert data into child. When inserting, the value of parent_id in child must be the data in parent, otherwise the insertion will not succeed.

When you delete a parent record, the corresponding record in the child is also deleted;-- > because: on delete cascade

When updating parent records, no updates are given;-- > because there is no definition, restrict is used by default.

4.2

If child is as follows:

Mysql >

Create table child (id int not null primary key auto_increment,parent_id int

Index par_ind (parent_id)

Constraint fk_1 foreign key (parent_id) references

Parent (id) on update cascade on delete restrict)

Type=innodb

Use the above:

1)。

When the parent record can be updated, the corresponding record in the child will also be updated;-- > because: on update cascade

2)。

Cannot be a child table operation, affecting the parent table. Only the parent table can affect the child table.

3)。

Delete the foreign key:

Alter table child drop foreign key fk_1

Add a foreign key:

Alter table child add constraint fk_1 foreign key (parent_id) references

Parent (id) on update restrict on delete set null

(5) multiple foreign keys exist:

The product_ order table has foreign keys for the other two tables.

A foreign key refers to a two-column index in an product table. Another reference to the single-row index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL

Price DECIMAL

PRIMARY KEY (category, id)) TYPE=INNODB

CREATE TABLE customer (id INT NOT NULL

PRIMARY KEY (id)) TYPE=INNODB

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT

Product_category INT NOT NULL

Product_id INT NOT NULL

Customer_id INT NOT NULL

PRIMARY KEY (no)

-- double foreign keys

INDEX (product_category, product_id)

FOREIGN KEY (product_category, product_id)

REFERENCES product (category, id)

ON UPDATE CASCADE ON DELETE RESTRICT

-- single foreign key

INDEX (customer_id)

FOREIGN KEY (customer_id)

REFERENCES customer (id)) TYPE=INNODB

(6) description:

1. If on update/delete is not declared, the default is restrict.

two。 For foreign key constraints, it is best to use: ON UPDATE CASCADE ON DELETE RESTRICT.

This is the end of the method of setting foreign key constraints in mysql. I hope the above content can be helpful to you and 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.