mysql 外键约束备注

梳理mysql外键约束的知识点。

1、mysql外键约束只对InnoDb引擎有效；

2、创建外键约束如下：

DROP TABLE IF EXISTS t_demo_product;

CREATE TABLE IF NOT EXISTS t_demo_product(

    proid int(20),

    proname varchar(20),

    price int(10),

    PRIMARY KEY(proid)

)ENGINE=InnoDB  DEFAULT CHARSET=gbk;

DROP TABLE IF EXISTS t_demo_operation;

CREATE TABLE IF NOT EXISTS t_demo_operation(

    opid  int(10),

    proid int(20),

    opcount int(10),

    PRIMARY KEY(opid),

    CONSTRAINT `fk_proid_id` FOREIGN KEY (`proid`) REFERENCES `t_demo_product` (`proid`)

)ENGINE=InnoDB  DEFAULT CHARSET=gbk;

3、执行插入操作错误：

insert into t_demo_operation(opid, proid, opcount) values

(1,7,2);

//Cannot add or update a child row: a foreign key constraint fails (`test`.`t_demo_operation`, CONSTRAINT `fk_proid_id` FOREIGN KEY (`proid`) REFERENCES `t_demo_product` (`proid`))

4、执行删除操作错误：

delete * from t_demo_product where proid = 2

//Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_demo_operation`, CONSTRAINT `fk_proid_id` FOREIGN KEY (`proid`) REFERENCES `t_demo_product` (`proid`))

mysql 外键约束备注的相关教程结束。