外键约束

来源：互联网发布：深圳乐易网络怎么样编辑：程序博客网时间：2024/06/04 18:41

1.创建class，课程表
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`scno` varchar(10) NOT NULL,
`scname` varchar(10) default NULL,
PRIMARY KEY (`scno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.创建student，学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` varchar(10) NOT NULL,
`sname` varchar(10) default NULL,
`scno` varchar(10) default NULL,
PRIMARY KEY (`sid`),
KEY `s_sc` (`scno`),
CONSTRAINT `s_sc` FOREIGN KEY (`scno`) REFERENCES `class` (`scno`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.实例
//插入时外键不存在于外键表中时插入不成功insert into student(sid,sname,scno)values('2','222','10')
//没有外键可以插入insert into student(sid,sname)values('1','111');
//课程表里有数据才可以insert into class(scno,scname)values('10','aaa');insert into student(sid,sname,scno)values('2','222','10')

0 0