mysql外键的应用_MySQL外键应用

(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如下:

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;

4)

添加外键:

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 的方式.