表和表之间可存在引用关系,这在抽象数据到表时,是很常见的。这种联系是通过在表中创建外键(foreign key)来实现的。 比如一个订单,可能关联用户表和产品表,以此来记录谁买了什么产品。 约定两个概念: 父表:被引用的表。 示例: CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) 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
) ENGINE=INNODB;
这里 外键关联表的同步操作当表和表之间建立起合适的关联后, 建立外键时,可指定
添加外键创建外键的语法[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
文章开头提到的订单表示例: CREATE TABLE product (
category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=INNODB;
CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=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),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
所以这个关系里有两个父表 对现有表添加外键可使用如下的语句: ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
创建外键时可指定 删除外键同样是通过 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
删除外键时,如果该外键在创建时取了名称,名通过该名称来删除,如果没有,则需要先查询 MySQL 在创建该外键时自动生成的名称 mysql> SHOW CREATE TABLE dept_manager\G
*************************** 1. row ***************************
Table: dept_manager
Create Table: CREATE TABLE `dept_manager` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) COLLATE utf8mb4_general_ci NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `dept_no` (`dept_no`),
CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
相关资源 |
转载于:https://www.cnblogs.com/Wayou/p/mysql_foreign_key.html