10
2014
03

Mysql外键设置(转)

(1) 外键的使用:

      外键的作用,主要有两个: 

      一个是让数据库自己通过外键来保证数据的完整性和一致性

      一个就是能够增加ER图的可读性 

      有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于没有通过外键的检测而使得开发人员删除,插入操作失败.其实这正式外键在强制保证数据的完整性和一致性.


(2) 添加外键的格式: 

    ALTER TABLE yourtablename 

    ADD [CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...) 

    REFERENCES tbl_name (index_col_name, ...) 

    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 

    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 

说明: 

on delete/on update,用于定义delete,update操作.以下是update,delete操作的各种约束类型: 

CASCADE: 

       外键表中外键字段值会被更新,或所在的列会被删除. 

RESTRICT: 

      RESTRICT也相当于no action,即不进行任何操作.即,拒绝父表update外键关联列,delete记录.

set null: 

      被父表的外键关联字段被update ,delete时,子表的外键列被设置为null. 

而对于insert,子表的外键列输入的值,只能是父表外键关联列已有的值.否则出错.


外键定义服从下列情况:(前提条件) 

1) 所有tables必须是InnoDB型,它们不能是临时表.因为在MySQL中只有InnoDB类型的表才支持外键.

2) 所有要建立外键的字段必须建立索引. 

3) 对于非InnoDB表,FOREIGN KEY子句会被忽略掉。 

注意: 

创建外键时,定义外键名时,不能加引号. 

如: constraint 'fk_1' 或 constraint "fk_1"是错误的 


(3) 查看外键: 

SHOW CREATE TABLE ***;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置. 

删除外键: 

alter table drop foreign key '外键名'. 

注意: 只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除. 若不定义,则可以: 

先输入:alter table drop foreign key -->会提示出错.此时出错信息中,会显示foreign key的系统默认外键名.---> 

用它去删除外键.


(4) 举例

实例一: 

4.1 

CREATE TABLE parent( id INT NOT NULL,  PRIMARY KEY (id) ) TYPE=INNODB;   -- type=innodb 相当于 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 

) TYPE=INNODB; 

向parent插入数据后,向child插入数据,插入时,child中的parent_id的值只能是parent中有的数据,否则插入不成功; 

删除parent记录时,child中的相应记录也会被删除;-->因为: on delete cascade 

更新parent记录时,不给更新;-->因为没定义,默认采用restrict. 

4.2 

若child如下: 

mysql> create table child(id int not null primary key auto_increment,parent_id int, index par_ind (parent_id), 

       constraint fk_1 foreign key (parent_id) references 

       parent(id) on update cascade on delete restrict ) type=innodb; 

用上面的: 

1). 则可以更新parent记录时,child中的相应记录也会被更新;-->因为: on update cascade 

2). 不能是子表操作,影响父表.只能是父表影响子表. 

3). 删除外键: alter table child drop foreign key fk_1; 

添加外键: 

alter table child add constraint fk_1 foreign key (parent_id) references 

parent(id) on update restrict on delete set null; 


(5) 多个外键存在:


product_order表对其它两个表有外键。 

一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引: 

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, 

                      price DECIMAL, 

                      PRIMARY KEY(category, id)) TYPE=INNODB; 

CREATE TABLE customer (id INT NOT NULL, 

                      PRIMARY KEY (id)) TYPE=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)) TYPE=INNODB;


(6) 说明:


1.若不声明on update/delete,则默认是采用restrict方式. 

2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.


外键名自己定义,栏目名采用当前表里的userid,参考数据库则是A表user所在的数据库,引用表为user,外栏目名即外键为user里面的userid,删除时,即当user表内的数据删除时本表所采取的动作,刷新时,本表所采取的动作。

:ON DELETE ON UPDATE ,动作有RESTRICT: 拒绝对父表的删除或更新操作.cascade从父表删除或更新且自动删除或更新子表中匹配的行.no action不做任何操作(默认),SETNULL设置为空。

« 上一篇下一篇 »

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。