MySQL约束
1、概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据 2、目的:保证数据库中数据的正确、有效性和完整性 3、分裂:
约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一的、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK 外键约束 用来让两张表的数据之间建立联系,保证数据的一致性和完整性 FOREIGN KEY
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
CREATE TABLE USER (
ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键' ,
NAME VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT '姓名' ,
AGE INT CHECK ( AGE > 0 AND AGE <= 120 ) COMMENT '年龄' ,
STATUS CHAR ( 1 ) DEFAULT '1' COMMENT '状态' ,
GENDER CHAR ( 1 ) COMMENT '性别'
) comment '用户表' ;
INSERT INTO USER ( NAME, AGE, STATUS , GENDER) VALUES ( 'TOM1' , 19 , '1' , '男' ) , ( 'TOM2' , 25 , '0' , '男' ) ;
外键约束
概念:外检用来让两张表的数据之间建立联系,从而保证数据的一致性和完整性。 语法:
CREATE TABLE 表名(
字段名 数据类型,
. . .
[ CONSTRAINT ] [ 外键名称] FOREIGN KEY ( 外键字段名) REFERENCES 主表 ( 主表列名)
) ;
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段名) REFERENCES 主表 ( 主表列名) ;
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
行为 说明 NO ACTION 当在父表中删除/更新对应记录时,首先应检查该记录是否有对应外键,如果有则不允许删除/更新。 RESTRICT 当在父表中删除/更新对应记录时,首先应检查该记录是否有对应外键,如果有则不允许删除/更新。 CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,若果有,则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null) SER DEFAULT 附表有变更时,子表将外键设置成一个默认值
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE TABLE DEPT(
ID INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY ,
NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称'
) COMMENT '部门表' ;
INSERT INTO DEPT( ID, NAME) VALUES ( 1 , '研发部' ) , ( 2 , '市场部' ) , ( 3 , '财务部' ) , ( 4 , '销售部' ) , ( 5 , '总经办' ) ;
CREATE TABLE EMP(
ID INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY ,
NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名' ,
AGE INT COMMENT '年龄' ,
JOB VARCHAR ( 20 ) COMMENT '职位' ,
SALARY INT COMMENT '薪资' ,
ENTRYDATE DATE COMMENT '入职时间' ,
MANAGERID INT COMMENT '直属领导ID' ,
DEPT_ID INT COMMENT '部门ID'
) COMMENT '员工表' ;
INSERT INTO EMP( ID, NAME, AGE, JOB, SALARY, ENTRYDATE, MANAGERID, DEPT_ID) VALUES ( 1 , '金庸' , 66 , '总裁' , 20000 , '2000-01-01' , NULL , 5 ) ,
( 2 , '张无忌' , 20 , '项目经理' , 12500 , '2005-12-05' , 1 , 1 ) ,
( 3 , '杨逍' , 33 , '开发' , 8400 , '2000-11-03' , 2 , 1 ) ,
( 4 , '韦一笑' , 48 , '开发' , 11000 , '2002-02-05' , 2 , 1 ) ,
( 5 , '常迎春' , 43 , '开发' , 10500 , '2004-09-07' , 3 , 1 ) ,
( 6 , '小昭' , 19 , '程序员鼓励师' , 6600 , '2004-10-12' , 2 , 1 ) ;
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY ( DEPT_ID) REFERENCES DEPT( ID) ;
ALTER TABLE EMP DROP FOREIGN KEY FK_EMP_DEPT_ID;
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY ( DEPT_ID) REFERENCES DEPT( ID) ON UPDATE CASCADE ON DELETE CASCADE ;
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY ( DEPT_ID) REFERENCES DEPT( ID) ON UPDATE SET NULL ON DELETE SET NULL ;