约束(constraint)简介
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。
约束分类
- 非空约束(not null)
- 默认约束 (default)
- 唯一性约束(unique)
- 主键约束(primary key)PK
- 外键约束(foreign key)FK
- 检查约束 (目前MySQL不支持、Oracle支持)
约束的使用
1、非空约束
非空约束指字段的值不能为空。
mysql> create table tb_emp6(
-> id int(11) not null,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> desc tb_emp6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
对于使用了非空约束的字段,如果用在添加数据时没有指定值,数据库系统会报错
mysql> insert into tb_emp6 (name) values("张三");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
在创建后的表中添加约束
alter table <表名> modify <列名> <数据类型> not null;
删除非空约束
alter table <表名> modify <列名> <数据类型>
2、默认约束
默认约束指定字段的默认值,如果添加的数据没为字段指定值,那么系统会自动的给该字段指定默认值
mysql> create table tb_emp8(
-> id int(11),
-> name varchar(32),
-> age int(11) default 18
-> );
mysql> insert into tb_emp8 (id,name) values (1,"张三");
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_emp8;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 张三 | 18 |
+------+------+------+
1 row in set (0.00 sec)
在创建后的表中添加默认约束
alter table <表名> modify <列名> <数据类型> default <默认值>;
删除默认约束
alter table <表名> modify <列名> <数据类型>
3、唯一性约束
唯一性约束(Uniqe Constraint )要求该列唯一,允许为空但是只能出现一个空值。唯一性约束可以确保一列后者几列不出现重复值
列级约束
mysql> create table tb_empl9(
-> id int(11) unique, //约束添加在列后
-> name varchar(32)
-> );
表级约束
mysql> create table tb_empl9(
-> id int(11),
-> name varchar(32),
-> unique(id) //约束添加在创建表后
-> );
插入id相同数据报错
mysql> insert into tb_empl9 values(1,"张三");
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_empl9 values(1,"李四");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
给多个字段联合约束
mysql> create table tb_empl10(
-> id int(11),
-> name varchar(32),
-> unique(id,name)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> insert into tb_empl10 values(1,"张三");
Query OK, 1 row affected (0.01 sec)
//插入与第一条id相同的数据,没问题
mysql> insert into tb_empl10 values(1,"李四");
Query OK, 1 row affected (0.01 sec)
//插入与第一条name相同的数据,没问题
mysql> insert into tb_empl10 values(2,"张三");
Query OK, 1 row affected (0.01 sec)
//插入与第一条name和key都报错的数据,这时候就会报错
mysql> insert into tb_empl10 values(1,"张三");
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'id'
mysql>
删除唯一性约束
alter table 表名 drop index 唯一约束名;
4、主键约束
主键约束(Primary key Constraint)要求主键列的数据唯一,并且不允许为空,是这条数据的唯一标识。
单字段主键
① 列级主键
mysql> create table tb_empl11(
-> id int(11) primary key,
-> name varchar(32)
-> );
② 表级主键
mysql> create table tb_empl12(
-> id int(11),
-> name varchar(32),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.10 sec)
多字段联合主键
mysql> create table tb_empl13(
-> id int(11),
-> name varchar(32),
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.09 sec)
alter添加主键
alter table <表名> add primary key(字段名)
alter table <表名> modify <字段名> <数据类型> primary key
alter table add constraint <主键名> primary key(字段名)
删除主键约束
alter table <表名> drop primary key;
5、外键约束
外键是表(从表)中的一个字段,对应另一个表(主表)中的主键。
外键用来在两个表的数据之间建立连接,外键的作用是保持数据的一致性、完整性。
例如:部门表tb_dept的主键是id,在员工表tb_empl中有一个deptId与这个id关联,这就是外键约束。
定义外键后,不允许删除在主表中具有关联关系的行
创建外键约束
//在从表中定义
[constraint <外键名>] foreign key <字段名> references <主表名> <主键列>
相关定义:
①、主表(父表):对于两个具有关联关系的表,相关字段中主键虽在的那个表即是主表
②、从表(子表):对于两个具有关联关系的表,外键所在的表
③、外键名:外键约束的名称
④、字段名:字表需要添加外键约束的字段名
⑤、主表名:被字表外键所依赖的表名
⑥、主键列:主表中定义的主键,或者列组合
例子:
//主表
mysql> create table tb_dept(
-> id int(11) primary key,
-> name varchar(22) not null,
-> location varchar(50)
-> );
Query OK, 0 rows affected (0.09 sec)
//从表
mysql> create table tb_empl(
-> id int(11) primary key,
-> name varchar(25),
-> deptId int(11),
-> salary float,
-> constraint fk_emp_dept foreign key(deptId) references tb_dept(id)
-> );
Query OK, 0 rows affected (0.09 sec)
如果删除主表(tb_dept)
mysql> drop table tb_dept;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
如果想顺利删除主表,有两种方法:
1、先删除从表,再删除主表
2、删除从表的外键约束
创建表后添加外键约束
ALTER TABLE <从表名> ADD CONSTRAINT <外键名> FOREIGN KEY(从表字段名) REFERENCES <主表名>(主表主键列)
删除外键约束
alter table 表名 drop foreign key 外键名;