外键约束

总结：
1、父表类型 innodB
2、子表在外键的字段上建立索引 index

create database test;
use demo;


user表：
create table user
(
userid integer not null auto_increment primary key,
username varchar(12) not null
)
type=innodb;



主要是在建password表的时候，对userid列要加 index，即：
create table password
(
userid integer not null,
password varchar(12) not null,
index (userid),
foreign key (userid) references user (userid)
on delete cascade
on update cascade
);


在user表中插进一些数据：
userid  username
1        jimy
2        tom
3        jean
在password 表中插进一些数据：
userid  password
1        jimypass
2        tompass
3        jeanpass

insert into user (username) values ('jimy');
insert into user (username) values ('tom');
insert into user (username) values ('jean');


insert into password (password) values ('jimypass');
//这样的语句是错误的

外键关联式用于由于外键参考的主键的元素改变时，外键元素也做相应改变
在增加数据的时候要把外键参考主键的值输进去，外键才能和相应的主表行建立起关系


insert into password (userid,password) values (1,'jimypass');

在user表中删除 jimy，它的password记录也应该被“级联删除”，
delete from user where username='jimy';
//成功，结果在password表中也被删除


除了 CASCADE 外，还有 RESTRICT(禁止主表变更)、SET NULL(子表相应字段设置为空)等操作。

InnoDB拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的INSERT或UPDATE操作。一个父表有一些匹配的行的子表，InnoDB对任何试图更新或删除该父表中候选键值的UPDATE或DELETE操作有所动作，这个动作取决于用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的referential action。当用户试图从一个父表删除或更新一行之时，且在子表中有一个或多个匹配的行，InnoDB根据要采取的动作有五个选择：
· CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间，你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
· SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
· NO ACTION: 在ANSI SQL-92标准中，NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒绝对父表的删除或更新操作。
· RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的）。

· SET DEFAULT: 这个动作被解析程序识别，但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。



no action 实验


user表：
create table user
(
userid integer not null auto_increment primary key,
username varchar(12) not null
)
type=innodb;



主要是在建password表的时候，对userid列要加 index，即：
create table password
(
userid integer not null,
password varchar(12) not null,
index (userid),
foreign key (userid) references user (userid)
on delete no action
on update no action               //no action 有空格  级联关系写在子表
);

insert into user (username) values ('jimy');
insert into user (username) values ('tom');
insert into user (username) values ('jean');


insert into password (userid,password) values (1,'jimypass');  //先有外键参考的列值，才能插入

delete from user where username='jimy';
//删除失败
 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`test/password`, CONSTRAINT `password_ibfk_1` FOREIGN KEY (`userid`) R
EFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION)

delete from password where userid=1;
delete from user where username='jimy';
//删除成功

总结：在子类中有参考父类键值时，父类行的更新和删除都将失败