全部博文(147)
分类: Mysql/postgreSQL
2008-10-15 00:15:07
从mysql
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
外键约束须满足以下条件:
. 父子表都为innodb,不是临时表
. 在子表上,必须存在一个索引,外键列是索引列的全部或部分,但必须是开头部分,并且顺序一致; 从mysql
. 在父表上,必须存在一个索引,被参照键是索引列的全部或部分,但必须是开头部分,并且顺序一致; 加速约束检查/避免全表扫描
. 不允许在外键前缀或后缀上索引; 外键不能包括text/blob列
. 如果指定约束标识符 symbol, 那么必须数据库范围唯一; 不指定时,系统会自动产生
. 父子表的相应列必须有相似的内部数据类型, 以便进行比较时不必进和类型转换
对于数字类型,类型长度与符号必须相同;
对于字符类型, 长度不必相同
. 如果创建一个on delete set null或on update set null约束,子表的列必须不能为not null
. 从mysql
外键约束对子表的含义:
如果在父表中找不到候选键,则不允许在子表上进行insert/update
外键约束对父表的含义:
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式, 分列如下
. cascade方式
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
On delete cascade从mysql
. set null方式
在父表上update/delete记录时,将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
On delete set null从mysql
. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
这个是ANSI SQL-92标准,从mysql
. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式
解析器认识这个action,但Innodb不能识别,不知道是什么意思...
注意:trigger不会受外键cascade行为的影响,即不会解发trigger
在mysql中,与SQL标准相违背的三点
1. 如果在父表中有多个key值相同,那么在进行外键check时,会当成有相同key值的其他行不存在; 比如当定义了一个restrict行为外键时,一个子表行对应到多个父表行(具有相同key值), Innodb不允许删除父表上的所有这些行
下面这句未理解,depth-first?
InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.
2. 父子表是同一个表,自我参照时不允许指定on update cascade, on update set null
从mysql
从mysql
但级联层次不能超出15
3, Innodb在检查unique,constraint约束时,是row by row而不是语句或事务结束;
SQL标准中对constraint的检查是在语句执行完成时
创建/操作外键的例子
--简单外键
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,id), è外键列是索引列的开头部分
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
)
engine=INNODB;
--较复杂的外键
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),
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)
)
engine=INNODB;
--查看外建/drop外键/alter 添加外键
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`,`id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table child drop FOREIGN KEY child_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table child add constraint child_ibfk_1
-> FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql中与外键相关的错误
If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message
string refers to errno 150, this means that the table creation failed because a foreign key constraint
was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that
means a foreign key definition would be incorrectly formed for the altered table. Starting from
MySQL
latest InnoDB foreign key error in the server.
测试如下:
set FOREIGN_KEY_CHECKS = 0; --禁用约束检查
drop table product; --删除product_order的父表
set FOREIGN_KEY_CHECKS = 1; --启用约束检查
重新创建product,这次没有建pk
CREATE TABLE product
(
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
index(category, id)
)
engine=INNODB;
查看show innodb status中的错误信息
------------------------
LATEST FOREIGN KEY ERROR
------------------------
080424 19:38:50 Cannot drop table `test/product`
because it is referenced by `test/product_order`
其它
1, mysqldump在dump数据时,会加入外键信息
2, 也可以用SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';查看外键信息, 在comment列中
3, 注意innodb中如果设置了lower_case_table_names,对外键的影响
4, 在创建外键时,可以加入backtricks(反引号),将外键名,列名包括起来
5, 如果是在mysql
6, Innodb要进行外键check时,需要在父或子表上加共享行级锁(shared row lock),不是事务级
7, 从mysql
对于早期版本,可以用下面方法达到同样目的
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;
8, 在alter table或load data时,可能也需要进行上述设置以临时禁止外键约束检查
9, 除非设置了FOREIGN_KEY_CHECKS = 0, Innodb禁止drop父表(被子表外键引用的表)
10, 当设置FOREIGN_KEY_CHECKS = 0后drop一个父子.
下次重新创建这个父表时,必须跟drop之前一样(正确的列/数据类型/被参照的列上必须有索引), 否则系统会报1005 refer to errno 150 (这一点前述测试中没有发现报错)
chinaunix网友2009-08-05 09:18:29
如果子表外键类型是varchar的时候 可以和父表的主键建立外键吗?当然父表的主键也是varchar而且编码等等都一致的。你这里举例的都是int类型的。