约束(Constraint)
约束在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束:
-
非空约束(not null):约束的字段不能为NULL
-
唯一约束(unique):约束的字段不能重复
-
主键约束(primary key):约束的字段既不能为NULL,也不能重复,简称PK
-
外键约束(foreign key)简称FK
-
检查约束(check)
注意:Oracle数据库有check约束,但是MySQL没有,目前不支持该约束
非空约束 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,'lisi','123');
mysql> select * from t_user;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | lisi | 123 |
+------+----------+----------+
1 row in set (0.00 sec)
唯一性约束 unique
唯一约束的字段具有唯一性,不能重复,但可以为NULL。
案例:给某一列添加unique
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique//列级约束
);
mysql> insert into t_user values(1,'zhangsan');
Query OK, 1 row affected (0.08 sec)
mysql> insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'//username重复了
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
mysql> select * from t_user;
+------+----------+
| id | username |
+------+----------+
| 1 | zhangsan |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+------+----------+
4 rows in set (0.00 sec)
案例:给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username)//多个字段联合添加约束
//表级约束
);
insert into t_user values(1,'111','zs');//这里相当于111zs
insert into t_user values(2,'111','ls');//这里相当于111ls
insert into t_user values(3,'222','zs');//这里相当于222zs
//都不一样
mysql> select * from t_user;
+------+----------+----------+
| id | usercode | username |
+------+----------+----------+
| 1 | 111 | zs |
| 2 | 111 | ls |
| 3 | 222 | zs |
+------+----------+----------+
3 rows in set (0.00 sec)
insert into t_user values(4,'111','zs');
ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'
注意:not 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@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');
mysql> select * from t_user;
+----+----------+------------+
| id | username | email |
+----+----------+------------+
| 1 | zs | zs@123.com |
| 2 | ls | ls@123.com |
| 3 | ww | ww@123.com |
+----+----------+------------+
3 rows in set (0.00 sec)
insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t_user(id,username,email) values('jack','jack@123.com');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
根据以上的测试得出:id是主键,因为添加了主键约束,主键字段中的数据不能为NULL,也不能重复。
主键的特点:不能为NULL,也不能重复。
主键约束相关的术语
- 主键约束
- 主键字段
- 主键值
id int primary key,
主键约束:primary key
主键字段:id字段添加primary key之后,id叫做主键字段
主键值:id字段中的每一个值都是主键值
主键作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样)
注意:一张表的主键约束只能有1个(必须记住)!!!
主键的分类
根据主键字段的字段数量来划分:
- 单一主键(推荐的、常用的)
- 复合主键(多个字段联合起来添加一个主键约束,不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
- 自然主键:主键值最好就是一个和业务没有任何关系的自然数(推荐)
- 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键、拿着身份证的身份证号码作为主键,不推荐使用。
使用表级约束方式定义主键
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ww');
insert into t_user(id,username) values(4,'zl');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | ls |
| 3 | ww |
| 4 | zl |
+----+----------+
4 rows in set (0.00 sec)
insert into t_user(id,username) values(4,'zx');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
MySQL提供了主键值自增
primary key auto_increment
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,//id字段自动维护一个自增的数字,从1开始,以1递增
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');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
+----+----------+
6 rows in set (0.00 sec)
Oracle也提供了一个自增机制:序列(sequence)对象
外键约束
外键约束相关术语
- 外键约束:foreign key
- 外键字段:添加有外键约束的字段
- 外键值:外键字段中的每一个值
业务背景:请设计数据库表,用来维护学生和班级的信息
第一种方案:一张表存储所有数据
no(pk) | name | classno | classname |
---|---|---|---|
1 | 阿波 | 101 | xx1班 |
2 | 波波 | 102 | xx2班 |
… | … | … | … |
缺点:冗余,不推荐
第二种方案:两张表(班级表和学生表)
t_class 班级表
cno(pk) | cname |
---|---|
101 | xx1班 |
102 | xx2班 |
t_student 学生表
sno(pk) | sname | cno(该字段添加外键约束fk) |
---|---|---|
1 | 阿波 | 101 |
2 | 波波 | 102 |
t_student中的cno字段引用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),
cno int,
primary key(sno),
foreign key(cno) 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',102);
mysql> select * from t_class;
+-----+------------------+
| cno | cname |
+-----+------------------+
| 101 | xxxxxxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyyyyy |
+-----+------------------+
2 rows in set (0.00 sec)
mysql> select * from t_student;
+------+-------+------+
| sno | sname | cno |
+------+-------+------+
| 1 | zs1 | 101 |
| 2 | zs2 | 102 |
+------+-------+------+
2 rows in set (0.00 sec)
insert into t_student values(3,'ww',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cqd`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`cno`))
//错误原因:cno没有103这个数据
注意:
- 外键可以为NULL
- 被引用的字段不一定是主键,但至少具有unique约束