删除列级完整性约束_05、约束（constraint）

1、什么是约束？

​ 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

​ 常见的约束：

2、非空约束（not null）

drop table if exists t_user;

create table t_user(
    id int,
    username varchar(255) not null,
    password varchar(255) 
);

insert into t_user(id,password) values (1,'123');   //错误的
ERROR 1364 (HY000): Field 'username' doesn't have a default value

insert into t_user(id,username,password) values(1,'张三','123');

3、唯一性约束（unique）

唯一性约束修饰的字段具有唯一性，不能重复。但可以为NULL。

• 在列后面直接加unique约束是列级约束
• 在列的下面加unique约束是表级约束

drop table if exists t_user;

create table t_user(
    id int,
    username varchar(255) unique,
    password varchar(255) 
);

insert into t_user(id,username,password) values (1,'张三','123'); 

insert into t_user(id,username,password) values(2,'张三','456');  //错误
ERROR 1062 (23000): Duplicate entry '张三' for key 'username'

insert into t_user(id,password) values(2,'456');    //唯一性可以为NULL
insert into t_user(id,password) values(2,'456');    //唯一性可以为NULL，因为NULL不是数值，所以不会比较重复性

案例：给一张表的两列添加唯一性约束

drop table if exists t_user;

create table t_user(
    id int,
    username varchar(255) ,
    email varchar(255),
    unique (username,email)
);
insert into t_user(id,username,email) values (1,'张三','123');
insert into t_user(id,username,email) values (1,'张三','456');//不会报错，除非两列都重复了

4、主键约束（primary key）

主键约束，代表着主键不能为NULL，也不能重复。

以下是单一主键：

drop table if exists t_user;
create table t_user(
    id int primary key,
    username varchar(255),
    email varchar(255)
);

insert into t_user(id,username,email) values(1,'zs','zs@qq.com');
insert into t_user(id,username,email) values(2,'ls','ls@qq.com');
insert into t_user(id,username,email) values(3,'ww','ww@qq.com');

insert into t_user(id,username,email) values(1,'ml','ml@qq.com');       //错误的，主键不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into t_user(username,email) values('ml','ml@qq.com');
//错误的，主键不能为NULL
ERROR 1364 (HY000): Field 'id' doesn't have a default value

以下是复合主键：（不建议使用）

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255),
    email varchar(255),
    primary key (id,username)
);

insert into t_user(id,username,email) values(1,'zs','zs@qq.com');
insert into t_user(id,username,email) values(2,'ls','ls@qq.com');
insert into t_user(id,username,email) values(3,'ww','ww@qq.com');

insert into t_user(id,username,email) values(1,'ml','ml@qq.com');       //正确的

insert into t_user(id,username,email) values(1,'zs','ml@qq.com');       //错误的，主键重复
ERROR 1062 (23000): Duplicate entry '1-zs' for key 'PRIMARY'

insert into t_user(username,email) values('zs','ml@qq.com');            //错误的，主键列为NULL
ERROR 1364 (HY000): Field 'id' doesn't have a default value

主键有什么作用？

• 表的设计三范式中有要求，第一范式就说要求任何一张表都应该有主键
• 主键的作用：主键值是这行记录在这张表当中唯一的标识。

任何表的主键约束只能有一个。

主键的分类：

• 根据主键字段和字段数量来划分：
• 单一主键
• 复合主键（多个字段联合到一起添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）
• 根据主键的性质来划分：
• 自然主键：主键值最好是与业务没有关系的自然数。
• 业务主键：主键值和系统 业务挂钩，比如：银行卡号，身份证号（不建议使用，最好不要把和业务挂钩的字段作为主键）。

mysql 提供的主键值自增：（primary key auto_increment）

提示：Oracle中也提供了一个自增的机制，叫做序列（sequence）

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,  //id为主键，并且可以自增,从1开始，以1自增
    username varchar(255),
    email varchar(255)
);

insert into t_user(username,email) values('zs','zs@qq.com');
insert into t_user(username,email) values('ls','ls@qq.com');
insert into t_user(username,email) values('ww','ww@qq.com');

5、外键约束（foreign key）

子表中某一列将父表的某一列作为外键，那么子表中的这一列中的值，就只能是父表中对应列有的，如果父表中没有的话，就会报错。

外键必须是另一个表的主键吗？

不一定是主键，但必须是唯一性索引。

外键值可以为NULL。

业务背景：

设计数据库表，来维护学生和班级信息：

班级表：t_class

| 列名 | 含义 | | --------- | -------- | | cno（pk） | 班级编号 | | cname | 班级名称 |

学生表：t_student

| 列名 | 含义 | | ------------- | -------- | | sno（pk） | 学生编号 | | sname | 学生姓名 | | classno（fk） | 班级编号 |

t_student 中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class表作为父表。

注意：

• 删除数据的时候，先删除子表，再删除父表。
• 添加数据的时候，先添加父表，再添加子表。
• 创建表的时候，先创建父表，再创建子表。
• 删除表的时候，先删除父表，再删除子表。

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
    cno int primary key,
    cname varchar(255)
);

create table t_student(
    sno int primary key,
    sname varchar(255),
    classno int,
    foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'高三(1)班');
insert into t_class values(102,'高三(2)班');

insert into t_student values(1,'张三',101);
insert into t_student values(2,'李四',101);
insert into t_student values(3,'王五',102);
insert into t_student values(4,'麻六',102);

insert into t_student values(5,'唐三',103);   //错误的，103在t_class中没有出现
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

insert into t_student (sno,sname) values(5,'唐三'); //正确，外键值可以为NULL。

select * from t_class;
select * from t_student;