17、约束

一、约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束

not null：非空，用于保证该字段的值不能为空，例如姓名、学号等

default：默认值，用于保证该字段有默认值，例如性别等

primary key：主键，用于保证该字段的值具有唯一性，并且非空，例如学号、员工编号等

列级：

alter table 表名 modify column 字段 字段类型 primary key;

表级：

alter table 表名 add primary key(字段);

union：唯一，用于保证该字段的值具有唯一性，可以为空，例如座位等

列级：

alter table 表名 modify column 字段 字段类型 unique;

表级：

alter table 表名 add unique(字段);

check：检查约束【mysql中不支持】

foreign key：外键，用于限制两张表的关系，用于保证该字段的值必须来自于主表关联列的值，在从表添加外键约束，用于引用主表中某列的值，例如学生表的专业编号等等

alter table 表名 add foreign key(从表字段) references 主表(字段);

可以通过以下2种方式来删除主表数据：

级联删除(on delete cascade)：如果删除了主表数据，和之相关联的从表数据都会被删除

设置级联删除：alter table 表名 add foreign key(从表字段) references 主表(字段) on delete cascade;

级联置空(on delete set null)：如果删除主表数据，和之相关联的从表相关联字段会被置为null：

设置级联置空：alter table 表名 add foreign key(从表字段) references 主表(字段) on delete set null;

外键：

1、在从表设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

3、主表的关联列必须是一个key（一般是主键或唯一，外键也可以但意义不大）

4、插入数据先插主表，删除数据先删从表

添加约束的时机：

1、创建表时

2、修改表时（表中不存在数据）

约束的添加分类：

1、列举约束：六大约束语法上都支持，但外键约束没有效果

2、表级约束：除了非空、默认，其他的都支持

查看表中所有的索引，包括主键、外键、唯一

show index from 表名;

唯一性 是否可以为空 一个表中可以有多个 是否可以多字段组合

主键 是 不可以 至多一个 可以，但不推荐

唯一 是 可以(bdb引擎中一列不能同时存在2个等于null的值) 多个 可以，但不推荐

 

 

二、创建表时添加约束：

1、添加列级约束（列级约束不支持起约束名）

语法：直接在字段名和类型后面追加约束类型即可

只支持：默认、非空、主键、唯一

-- 1、添加列级约束
CREATE TABLE stuinfo (
id INT PRIMARY KEY,# 主键
stuName VARCHAR ( 50 ) NOT NULL,# 非空
gender CHAR ( 2 ) CHECK ( gender = '男' OR gender = '女' ),# 检查  mysql无效果，仅做演示
seat INT UNIQUE,# 唯一
age INT DEFAULT 18,# 默认
majorId INT REFERENCES major ( id ) # 外键   列级约束无效果，仅做演示
);
CREATE TABLE major ( id INT PRIMARY KEY, majorName VARCHAR ( 20 ) );

2、添加表级约束（约束名不要用''括起来，否则会报错）

语法：在各个字段的最下面添加

constraint 约束名 约束类型(字段名) -- constraint 约束名 可以省略，如果省略约束名默认显示为字段名，主键约束名固定为primary，就算指定为其他名称也不生效

添加外键约束：

alter table 表名 add constraint 约束名 foreign key(字段名) references 主表名(字段名)

-- 2、添加列级约束
CREATE TABLE stuinfo (
    id INT,
    stuName VARCHAR ( 50 ),
    gender CHAR ( 2 ),
    seat INT,
    age INT,
    majorId INT,
    CONSTRAINT pk PRIMARY KEY ( id ),
    CONSTRAINT uq UNIQUE ( seat ),
    CONSTRAINT ck CHECK ( gender = '男' OR gender = '女' ),
CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorId ) REFERENCES major ( id ) 
);

-- 通用写法
CREATE TABLE if not exists stuinfo (
id INT PRIMARY KEY,# 主键
stuName VARCHAR ( 50 ) NOT NULL,# 非空
gender CHAR ( 2 ) CHECK ( gender = '男' OR gender = '女' ),# 检查  mysql无效果
seat INT UNIQUE,# 唯一
age INT DEFAULT 18,# 默认
majorId INT，
CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorId ) REFERENCES major ( id )  # 外键
);

 

 

三、删除约束

1、主键约束

alter table 表名 modify column 主键字段 字段类型;

alter table 表名 drop primary key;

2、唯一

alter table 表名 drop index 创建唯一约束时的约束名;

3、外键

alter table 表名 drop foreign key 创建外键约束时的约束名;

如果再添加外键约束时没有起别名，mysql会自动添加外键别名，可使用“show create table 表名;”查看

在删除外键后用“show index from 表名;”查看仍然能看到之前删除了外键的字段仍然能查询到，原因是因为MYSQL在建外键后,会自动建一个同名的索引，我们需要再一次删除“alter table 表名 drop index key_name;”(key_name一般默认为字段名)

4、默认

alter table 表名 modify column 添加默认约束字段 字段类型;

5、非空

alter table 表名 modify column 添加非空约束字段 字段类型 null;（null可以省略）

-- 1. 向表emp2的id列中添PRIMARY KEY约束 (my_emp_id_pk) 
-- 列级
alter table emp5 modify column employee_id varchar(50) primary key;
-- 表级
alter table emp5 add constraint my_emp_id_pk primary key(employee_id);
show index from emp5;
show create table emp5;
-- 2. 向表dept2的id列中添加PRIMARY KEY约束(mydept_id_pk) 
alter table dept2 add constraint mydept_id_pk primary key(department_id);
-- 3. 向表emp2中添加列deptid，并在其中定义FOREIGNKEY约束，与之相关联的列是dept2表中的id列
alter table emp5 add column deptid int;
alter table emp5 add constraint fk_dept2_emp5 foreign key(deptid) references dept2(department_id);