一、为什么要外键约束???
外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的完整性和一致性
二、如何创建外键?
外键的定义:一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY(建立外键的字段必须类型完全一样,如:int 和 bigint 或者 int unsigned 和 int 都是不行的,同时都是InnoDB) (有的 blog说只要类型相似就可以,真是误人子弟啊!!!!!)
区别:列级约束语法和表级约束语法
使用列级约束语法建立外键约束直接使用references关键字,references指定该列参照的哪个主表,以及参照主表的哪一列
虽然MySQL支持使用列级约束的语法来建立外键约束,但这种列级的约束语法建立的外键约束不会生效,MySQL提供这种列级约束语法仅仅是和标准SQL保持良好的兼容性。因此,如果需要MySQL中的外键约束生效,应使用表级约束语法使用表级约束语法,则可以需要使用foreign key来指定本表的外键列,并使用references来指定参照哪个主表,以及参照到主表的哪个列。使用表级约束语法可以使用外键的约束指定约束名,如果创建外键约束没有指定约束名,则MySQL会为该外键约束命名为table_name_ibfk_n,其中table_name是从表的表名,而n是从1开始的整数
代码如下:
persons 表
CREATE TABLE `persons` (
`Id_P` INT NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`Id_P`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Orders表:
CREATE TABLE Orders(
Id_O int NOT NULL AUTO_INCREMENT,
Id_P INT,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
这时可以建立persons和Orders外键约束关系persons为主表,Orders为从表。
注意:在创建外键约束是出现Foreign key constraint is incorrectly formed可能参与的字段类型类型不一致导致
如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束,请使用下面的 SQL
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
撤销 FOREIGN KEY 约束
如需撤销 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
参考地址:http://www.w3school.com.cn/sql/sql_foreignkey.asp
三、删除带有外键约束主表记录时出现的问题?????
- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/t_bid`, CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `t_item
- ` (`id`))
解决方法:级联删除,即在删除persons表中的记录时同时删除Orders表中的相关记录
(1)增加外键约束时声明级联删除,即:ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES persons(Id_P) ON DELETE CASCADE
或者级联更新:ALTER TABLE Orders ADD CONSTRAINT orders_fk_test FOREIGN KEY (Id_P) REFERENCES persons(Id_P) ON DELETE CASCADE ON UPDATE CASCADE
(2) 使用触发器:在删除persons表中记录之前先删除与之相关的Orders表中的记录。
- delimiter //
- create trigger tri_delete before delete on t_item
- for each row
- begin
- delete from t_bid where id = old.id;
- end //
-
从图可以看出::外键列必须要作为一个索引使用,如果这样的索引不存在,引用表需要自动创建索引