约束(Constraint)
1、什么是约束?常见的约束有哪些呢?
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有这些:
非空约束(not null):约束的字段不能为NULL
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)
外键约束(foreign key):...(简称FK)
检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql(5.5)不支持该约束。
1.1、非空约束 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'); // 错误
insert into t_user(id,username,password) values(1,'lisi','123'); // 正确
1.2、唯一性约束(unique)
* 唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。
* 案例:给某一列添加unique
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique // 列级约束
);
// 错误:
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
// 可以为null:
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
* 案例:给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
insert into t_user values(4,'111','zs'); // 错误,与第一条数据重复。
以下是为两个字段单独添加unique约束,不是联合判重:
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255) unique,
username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls'); // 错误,字段usercode重复。
* 注意:not null约束只有列级约束。没有表级约束。
1.3、主键约束
* 给一张表添加主键约束:
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@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
+----+----------+------------+
| id | username | email |
+----+----------+------------+
| 1 | zs | zs@123.com |
| 2 | ls | ls@123.com |
| 3 | ww | ww@123.com |
+----+----------+------------+
insert into t_user(id,username,email) values(1,'jack','jack@123.com'); // 错误,主键值重复。
insert into t_user(username,email) values('jack','jack@123.com'); // 错误,主键值为NULL。
主键的特点:不能为NULL,也不能重复。
* 主键相关的术语:
主键约束 : primary key
主键字段 : 添加了主键约束的字段
主键值 : 主键字段中的每一个值都是主键值。
* 主键有什么作用?
- 表的设计三范式中,第一范式就要求任何一张表都应该有主键。
- 主键的作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样。)
* 主键的分类?
根据主键字段的字段数量来划分:
单一主键(推荐的,常用的。)
复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
自然主键:主键值是一个和业务没有任何关系的自然数。(这种方式是推荐的)
业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要
随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
* 一张表的主键约束只能有1个。
* 使用表级约束方式定义主键:
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
* mysql提供主键值自增:
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+----------+
提示:Oracle当中也提供了一个自增机制,叫做:序列(sequence)对象。
1.4、外键约束
* 关于外键约束的相关术语:
外键约束: foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值。
* 业务背景:
请设计数据库表,用来维护学生和班级的信息?
第一种方案:一张表存储所有数据
no(pk) name classno classname
----------------------------------------------------------------
1 zs1 101 亦庄二中高三1班
2 zs2 101 亦庄二中高三1班
3 zs3 102 亦庄二中高三2班
4 zs4 102 亦庄二中高三2班
5 zs5 102 亦庄二中高三2班
缺点:冗余。【不推荐】
第二种方案:两张表(班级表和学生表)
t_class 班级表
cno(pk) cname
---------------------------------
101 亦庄二中高三1班
102 亦庄二中高三2班
t_student 学生表
sno(pk) sname classno(该字段添加外键约束fk)
------------------------------------------------------------
1 zs1 101
2 zs2 101
3 zs3 102
4 zs4 102
5 zs5 102
* 将以上表的建表语句写出来:
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,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_class;
select * from t_student;
insert into t_student values(7,'lisi',103); // 错误,只能添加父表中存在的值。
* 外键值可以为NULL。
* 外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
注意:被引用的字段不一定是主键,但至少具有unique约束。
修正后SQL语句(主键设置为自增自然数,被引用字段使用unique约束):
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
pk int primary key auto_increment,
cno int,
cname varchar(255),
unique(cno)
);
create table t_student(
pk int primary key auto_increment,
sno int,
sname varchar(255),
classno int,
unique(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class(cno,cname) values(101,'深圳大学');
insert into t_class(cno,cname) values(102,'深圳大学研究院');
insert into t_student(sno,sname,classno) values(180902,'zs1',101);
insert into t_student(sno,sname,classno) values(180903,'zs2',101);
insert into t_student(sno,sname,classno) values(180904,'zs3',102);
insert into t_student(sno,sname,classno) values(180905,'zs4',102);
insert into t_student(sno,sname,classno) values(180906,'zs5',102);
insert into t_student(sno,sname,classno) values(180907,'zs6',102);
select * from t_class;
select * from t_student;
+----+------+----------------+
| pk | cno | cname |
+----+------+----------------+
| 1 | 101 | 深圳大学 |
| 2 | 102 | 深圳大学研究院 |
+----+------+----------------+
2 rows in set (0.00 sec)
+----+--------+-------+---------+
| pk | sno | sname | classno |
+----+--------+-------+---------+
| 1 | 180902 | zs1 | 101 |
| 2 | 180903 | zs2 | 101 |
| 3 | 180904 | zs3 | 102 |
| 4 | 180905 | zs4 | 102 |
| 5 | 180906 | zs5 | 102 |
| 6 | 180907 | zs6 | 102 |
+----+--------+-------+---------+