mysql外键约束

一、为什么要外键约束？？？

外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的完整性和一致性

二、如何创建外键？

   外键的定义:一个表中的 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 OrdersADD FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL:

ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders

参考地址:http://www.w3school.com.cn/sql/sql_foreignkey.asp

三、删除带有外键约束主表记录时出现的问题？？？？？

1. 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  
2. ` (`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表中的记录。

1. delimiter //    
2. create trigger tri_delete before delete on t_item  
3. for each row  
4. begin  
5.      delete from t_bid where id = old.id;  
6. end //  
7. 
   

从图可以看出::外键列必须要作为一个索引使用，如果这样的索引不存在，引用表需要自动创建索引