MySQL 约束

Demo_Null

发布于 2020-09-28 17:43:31

3K0

发布于 2020-09-28 17:43:31

文章被收录于专栏：Java 学习

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;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/07/03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

云推荐引擎

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

云推荐引擎

登录后参与评论

0 条评论

热度

MySQL 约束

MySQL 约束

1.1 非空(not null)

1.1.1 概述

1.1.2 语法

1.1.3 示例

1.2 唯一(unique)

1.2.1 概述

1.2.2 语法

1.2.3 示例

1.3 主键(primary key)

1.3.1 概述

1.3.2 语法

1.3.3 示例

1.4 自动增长(auto_increment)

1.4.1 概述

1.4.2 语法

1.4.3 示例

1.5 外键(foreign key)

1.5.1 概述

1.5.2 语法

1.5.3 示例

1.6 默认值(Default)

1.6.1 概述

1.6.2 语法

1.6.3 示例

1.7 级联操作

1.7.1 级联更新(on uptate cascade)

1.7.2 级联删除(on delete cascade)

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐