How to set foreign key constraints in mysql: You can specify the foreign key through the FOREIGN KEY keyword, the syntax "ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (column name) REFERENCES main table name (column name) );".
(Recommended tutorial: mysql video tutorial)
(1) The use of foreign keys:
There are two main functions of foreign keys:
One is to let the database itself Ensuring the integrity and consistency of data through foreign keys
One is to increase the readability of the ER diagram
Some people think that the establishment of foreign keys will bring a lot of trouble to the database during development. Because the database Sometimes developers fail to delete or insert operations because they fail to pass the foreign key detection. They think this is very troublesome. In fact, this formal foreign key forces you to ensure the integrity and consistency of the data. This is a good thing.
For example:
There is a basic data table used to record all information about the product. The other tables all store product IDs. When querying, you need to join the table to query the name of the product. The product table of document 1 has a product ID field, and the product table of document 2 also has a product ID field
. If you do not use foreign keys, when documents 1 and 2 both use the product with product ID = 3, if you delete the corresponding record with ID = 3 in the product table, you will not be able to find the product when you view documents 1 and 2. The name. When there are only a few tables, some people think that the integrity and consistency of the data can be ensured by writing scripts when the program is implemented. That is, when deleting a product, check whether the product
with product ID 3 has been used in documents 1 and 2. But after you finish writing the script, the system adds a document 3, which also saves the product ID and finds a field. If you don't use foreign keys, you will still be unable to find the product name. You can't go back and modify your script to detect whether the product is used every time you add a document that uses the product ID field. At the same time, introducing foreign keys will reduce speed and performance.
(2) Format of adding 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 and update operations. The following are various constraint types for update and delete operations:
CASCADE:
Foreign keys in foreign key tables The field value will be updated, or the column in which it is located will be deleted.
RESTRICT:
RESTRICT is also equivalent to no action, that is, no operation is performed. That is, the parent table is refused to update the foreign key associated column and delete the record.
set null:
When the foreign key associated field of the parent is updated or deleted, the foreign key column of the child table is set to null.
And for insert, the value entered in the foreign key column of the child table, It can only be the existing value of the foreign key associated column of the parent table. Otherwise, an error occurs.
Foreign key definition is subject to the following conditions: (Prerequisite)1)
All tables They must be of InnoDB type, and they cannot be temporary tables. Because only InnoDB type tables support foreign keys in MySQL.
2)
All fields to be established as foreign keys must be indexed.
3)
For non-InnoDB tables, the FOREIGN KEY clause will be 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 key: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 foreign key:
alter table drop foreign key 'foreign key name'.
Note:
Only when defining a foreign key, use constraint foreign key name.... to facilitate the deletion of the foreign key.
If not defined, Then you can:
First enter: alter table drop foreign key --> An error will be prompted. At this time, the error message will display the system default foreign key name of the foreign key. --->
Use it to Delete foreign keys.
#(4) Example
Example 1:
4.1
CREATE TABLE parent(id INT NOT NULL,
but 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 the parent, insert data into the child. When inserting, the value of parent_id in the child can only be the data in the parent, otherwise the insertion will not succeed;
When deleting the parent record, the corresponding record in the child It will also be deleted;-->Because: on delete cascade
When updating the parent record, it will not be updated;-->Because it is not defined, the default is restrict.
4.2
If the 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 you update the parent record, the corresponding record in the child will also be updated;--> Because: on update cascade
2).
cannot be a child table operation that affects the parent table. It can only be a parent table that affects the child table.
3).
Delete foreign key:
alter table child drop foreign key fk_1;
Add 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 to the other two tables. A foreign key refers to a double-column index in the product table. Another one -line index quoted 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),
-- 双外键
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
-- 单外键
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
(6) 说明:
1.若不声明on update/delete,则默认是采用restrict方式.
2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
The above is the detailed content of How to set foreign key constraints in mysql?. For more information, please follow other related articles on the PHP Chinese website!