MySQL 中的外键
表和表之间可存在引用关系,这在抽象数据到表时,是很常见的。这种联系是通过在表中创建外键(foreign key)来实现的。 比如一个订单,可能关联用户表和产品表,以此来记录谁买了什么产品。 SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。约定两个概念: 父表:被引用的表。 示例: 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) 相关资源 |

更多精彩