SQL完整性约束
引言
完整性约束:用于限制字段的值必须满足一定的条件,从而保证数据表中的数据的一致性和完整性!!!
1.常见的约束:
- NOT NULL:非空 要求非空字段为必填项
- DEFAULT:默认值 如果该字段不手动插入值,有默认值
- PRIMARY KEY:主键 要求主键字段不能重复,且不能为空,一个表中只能有一个主键
- UNIQUE:唯一 要求唯一键的字段不能重复,可以为空,一个表中可以有多个唯一键
- CHECK:检查 mysql不支持,要求该字段必须满足指定的检查条件
- FOREIGN KEY:外键 用于限制两个表的关系
2.约束的特性:
-
只可以当做列级约束:非空、默认
-
只可以当做表级约束:外键
-
两者皆可:主键、唯一、检查
1.创建表时添加约束 ☆
例:
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT,
majorname VARCHAR(20)
);
1)写法1:使用列级约束 √
列级约束语法:字段声明 + 约束名
CREATE TABLE IF NOT EXISTS constraintTab(
id INT PRIMARY KEY, #主键
NAME VARCHAR(20) NOT NULL UNIQUE, #非空+唯一
gender CHAR DEFAULT '男', #默认
age INT CHECK(age BETWEEN 18 AND 120), #检查
seat INT UNIQUE,#唯一
majorId INT,
CONSTRAINT fk FOREIGN KEY (majorid) REFERENCES major(id) #外键:fk约束名
);
2)写法2:使用表级约束
表级约束语法:约束类型(字段名)
[constraint 约束名] 约束类型(字段名) [references 主表(字段名)]
案例:
CREATE TABLE IF NOT EXISTS constraintTab(
id INT,
NAME VARCHAR(20) NOT NULL , # 非空
gender CHAR DEFAULT '男', # 默认
age INT ,
seat INT ,
majorId INT,
CONSTRAINT fk FOREIGN KEY (majorid) REFERENCES major(id), #外键
PRIMARY KEY(id), #主键
CONSTRAINT uq UNIQUE(seat), #唯一
CONSTRAINT ck CHECK(age BETWEEN 10 AND 100) #检查
);
2.修改表时添加约束:Alter table …
创建案例表:
DROP TABLE IF EXISTS constraintTab;
CREATE TABLE IF NOT EXISTS constraintTab(
id INT,
NAME VARCHAR(20),
gender CHAR,
seat INT,
age INT,
majorid INT
);
1.添加主键:primary key
- 1)当做列级约束
ALTER TABLE constraintTab MODIFY COLUMN id INT PRIMARY KEY;
- 2)当做表级约束 √
ALTER TABLE constraintTab ADD PRIMARY KEY(id);
# 对比添加列:
ALTER TABLE stu ADD COLUMN classname varchar(100);
2.添加唯一键: UNIQUE
- 1)当做列级约束
ALTER TABLE constraintTab MODIFY COLUMN seat INT UNIQUE;
- 2)当做表级约束 √
ALTER TABLE constraintTab ADD UNIQUE(NAME);
3.添加检查约束:check
- 1)当做列级约束
ALTER TABLE constraintTab
MODIFY COLUMN age INT CHECK(age>=10 AND age<100);
- 2)当做表级约束 √
ALTER TABLE constraintTab
ADD CHECK(age>=10 AND age<100);
4.添加外键:foreign key
只能当做表级约束,所以使用添加字段定义的方式:add
ALTER TABLE constraintTab ADD
CONSTRAINT fk_major_stuinfo
FOREIGN KEY(majorid) REFERENCES major(id);
5.添加默认:default
只能当做列级约束,所以使用修改列的定义方式:modify
ALTER TABLE constraintTab MODIFY COLUMN gender CHAR DEFAULT '男';
6.添加非空:not null
只能当做列级约束,所以使用修改列的定义方式:modify
ALTER TABLE constraintTab MODIFY COLUMN name VARCHAR(20) NOT NULL;
3.删除约束:ALTER TABLE
1.删除主键:只有一个
ALTER TABLE constraintTab DROP PRIMARY KEY;
2.删除唯一:可以有多个,所以需指定字段名
ALTER TABLE constraintTab DROP INDEX seat;
3.删除外键: 可以有多个,所以需指定外键约束名
ALTER TABLE constraintTab DROP FOREIGN KEY constrainttab_ibfk_1;
4.删除非空:通过修改列属性
ALTER TABLE constraintTab MODIFY COLUMN name VARCHAR(20) NULL;
5.删除默认:通过修改列属性
ALTER TABLE constraintTab MODIFY COLUMN gender CHAR(1) ;
注意
1.自增长列
- 默认从1开始,步长为1
- 1、自增长列要求设置的字段类型为数值型
- 2、自增长列要求必须和键搭配使用
- 3、一个表中最多有一个自增长列
- 4、主键如果是自增长列(非空),添加记录时,可以选填
2.联合主键
创建表时,在constraint约束区域,声明指定字段为主键:
- 格式:
[constraint 名称] primary key (字段列表)
- 关键字constraint可以省略,如果需要为主键命名,constraint不能省略,主键名称一般没用。
- 字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。
- 声明两个以上字段为主键,我们称为联合主键。
查看表的索引:
SHOW INDEX FROM constraintTab;