MySQL 约束

小编:啊南 58阅读 2020.11.20

1.1 非空(not null)1.1.1 概述
  非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段,用户在添加数据时没有指定值,数据库系统就会报错。可以通过 CREATE TABLE 或 ALTER TABLE 语句实现非空。在表中某个列的定义后加上关键字 NOT NULL 作为限定词,来约束该列的取值不能为空。

1.1.2 语法
# 创建表时添加非空
create table tb_name (
col_name_1 col_type_1,
col_name_2 col_type_2 not null
);

# 修改表时添加非空
alter table tb_name modify column col_name col_type not null;

# 删除非空
alter table tb_name modify column col_name col_type;
1.1.3 示例
create table test (
id int(11) not null
);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test values(null);
1048 - Column 'id' cannot be null

mysql> alter table test modify column id int;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test values(null);
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.10 sec)
1.2 唯一(unique)1.2.1 概述
  唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。例如,为 id 字段加上唯一性约束后,每条记录的 id 值都是唯一的,不能出现重复的情况。如果其中一条记录的 id 值为 “0001”,那么该表中就不能出现另一条记录的 id 值也为 “0001”。唯一约束在一个表中可有多个,并且设置唯一约束的列允许有空值,但是只能有一个空值。

1.2.2 语法
# 创建时添加
create teble tb_name (
col_name_1 col_type_1 unique,
col_name_2 col_type_2
);

# 创建后添加,若添加唯一约束前含有相同数据则必须先处理数据后才能添加
alter table tb_name modify col_name col_type unique;

# 删除唯一约束
alter table tb_name drop index col_name;
1.2.3 示例
mysql> create table test (
    -> id int unique
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test values(1);
1062 - Duplicate entry '1' for key 'id'

mysql> alter table test drop index id;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test values(1);
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  1 |
+----+
2 rows in set (0.10 sec)


mysql> alter table test modify id int unique;
1062 - Duplicate entry '1' for key 'id'
1.3 主键(primary key)1.3.1 概述
  主键(PRIMARY KEY)的完整称呼是“主键约束”,是 MySQL 中使用最为频繁的约束。一般情况下,为了便于更快的查找到表中的记录,都会在表中设置一个主键。使用主键应注意以下几点:每个表只能定义一个主键;主键值必须唯一标识表中的每一行,且不能为 NULL,即表中不可能存在有相同主键值的两行数据且不能为空(主键非空且唯一)。

1.3.2 语法
# 创建时添加
create teble tb_name (
col_name col_type primary key
);

# 创建后添加
alter table tb_name modify col_name col_type primary key;

# 删除主键约束
alter table tb_name drop primary key; 
1.3.3 示例
mysql> create table test (
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.11 sec)


mysql> alter table test modify name varchar(20) primary key;
1068 - Multiple primary key defined

mysql> insert into test values (1, "张三");
Query OK, 1 row affected (0.06 sec)

mysql> insert into test values (2, "张三");
Query OK, 1 row affected (0.05 sec)

mysql> insert into test values (1, "张三");
1062 - Duplicate entry '1' for key 'PRIMARY'

mysql> alter table test drop primary key;
Query OK, 2 rows affected (19.62 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test values (1, "张三");
Query OK, 1 row affected (0.06 sec)
1.4 自动增长(auto_increment)1.4.1 概述
  在 MySQL 中,当主键定义为自增长后,这个主键的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录,主键会自动以相同的步长进行增长。通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。一个表中只能有一个字段使用 AUTO_INCREMENT 约束,且该字段必须有唯一索引,以避免序号重复(即自动增长只能给 primary key 或者 unique 添加,一张表中只能添加一个)。自增长列当前值存储在内存中,数据库每次重启之后,会查询当前表中自增列的最大值作为当前值。    AUTO_INCREMENT 的初始值是 1,每新增一条记录,字段值自动加 1。    AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。    AUTO_INCREMENT 约束的字段只能是整数类型(tinyint、smallint、int、bigint 等)。    AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束,如果达到上限,自动增长就会失效。

1.4.2 语法
# 创建时添加
create teble tb_name (
col_name col_type primary key auto_increment
);

# 创建后添加
alter table tb_name modify col_name col_type auto_increment;

# 删除自动增长
alter table tb_name modify col_name col_type; 
1.4.3 示例
mysql> create table test (
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values (null, '张三'), (null, '李四');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.15 sec)

mysql> insert into test values (2, '王五');
1062 - Duplicate entry '2' for key 'PRIMARY'

mysql> insert into test values (100, '王五');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test values (null, '马六'), (null, '田七');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-----+------+
| id  | name |
+-----+------+
|   1 | 张三 |
|   2 | 李四 |
| 100 | 王五 |
| 101 | 马六 |
| 102 | 田七 |
+-----+------+
5 rows in set (0.14 sec)
1.5 外键(foreign key)1.5.1 概述
  外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。主表删除某条记录时,从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键,外键可以为空值,若不为空值,则每一个外键的值必须等于主表中主键的某个值。定义外键时,需要遵守下列规则:    主表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性。    主键不能包含空值,但允许在外键中出现空值。也就是说,外键的每个非空值必须是主表主键存在内容。    若一个表中的字段被另一个表关联则该字段不能直接删除。

1.5.2 语法
# 创建时添加
create teble tb_name (
	col_name_1 col_type_1,
	col_name_2 col_type_2,
	[constraint 外键名称] foreign key 外键字段 references 关联表(关联字段)
);

# 创建后添加
alter table tb_name add [constraint 外键名称] foreign key 外键字段 references 关联表(关联字段);

# 删除外键
alter table tb_name drop foreign key 外键名称;
1.5.3 示例
# 部门表
mysql> create table dept (
    -> id int primary key, -- 部门id
    -> dname varchar(50), -- 部门名称
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into dept values (1, '技术部'), (2, '财务部'), (3, '运营部');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+----+--------+
| id | dname  |
+----+--------+
|  1 | 技术部 |
|  2 | 财务部 |
|  3 | 运营部 |
+----+--------+
3 rows in set (0.17 sec)

# 员工表
mysql> create table emp (
    -> id int primary key, -- 员工id
    -> ename varchar(50), -- 员工姓名
    -> dept_id int, -- 所在部门编号
    -> constraint emp_deptid_ref_dept_id_fk foreign key (dept_id) references dept (id)
    -> );
Query OK, 0 rows affected (0.10 sec)


mysql> insert into emp values (1, '张三', 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into emp values (2, '李四', 4);
1452 - Cannot add or update a child row: a foreign key constraint fails 
	 - (`emp`, CONSTRAINT `emp_deptid_ref_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

mysql> insert into emp values (2, '李四', null);
Query OK, 1 row affected (0.05 sec)
1.6 默认值(Default)1.6.1 概述

??默认值约束(Default Constraint),用来指定某列的默认值。在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。默认值约束通常用在已经设置了非空约束的列,这样能够防止数据表在录入数据时出现错误。

1.6.2 语法
# 建表时添加
create table tb_name (
	col_name col_type not null default default_value
);

# 建表后添加
alter table tb_name modify col_name col_type default default_value;

# 删除默认值
alter table tb_name modify col_name col_type default null;
1.6.3 示例
mysql> create table test (
    -> id int,
    -> name varchar(20) default '海绵宝宝'
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | 海绵宝宝 |
+----+----------+
1 row in set (0.11 sec)
1.7 级联操作1.7.1 级联更新(on uptate cascade)
alter table tb_name add [constraint 外键名称] foreign key 外键字段 references 关联表(关联字段) on uptate cascade;
1.7.2 级联删除(on delete cascade)
alter table tb_name add [constraint 外键名称] foreign key 外键字段 references 关联表(关联字段) on delete cascade;
关联标签: