mysql 数据库外键使用

1.创建表A,id为主键

create table A(id int unsigned primary key not null,name varchar(128) not null)Engine=InnoDB,CHARSET=utf8;

2.创建表B,id为来自于表A的id字段的外键,

create table B(id int unsigned primary key not null,foreign key(id) references A(id) on delete cascade on update cascade,score int unsigned default 0)ENGINE=InnoDB,CHARSET=utf8;

说明：
1.必须使用Engine=InnoDB,只有这种类型的引擎才支持外键;
2.如果B中外键id的值在A中不存在则插入时会报错:
insert into B (id,score) values(1,100); //此时A中不存在id=1的字段.
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`algoplat/B`, CONSTRAINT `B_ibfk_1` FOREIGN KEY (`id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)