【MySQL系列】表的常见约束_在关系型数据库中,以下哪个约束用于保证表中的某个列的取值在另一个表的某个列中-CSDN博客

数据库约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

主要介绍以下几个：

NOT NULL（非空约束） - 指示某列不能存储 NULL 值。
UNIQUE（唯一约束） - 保证某列的每行必须有唯一的值。
DEFAULT（缺省约束） - 规定没有给列赋值时的默认值。
PRIMARY KEY（主键约束） - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY（外键约束） - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK（检查约束） - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

空属性

两个值：null（默认的）和not null(不为空)

创建一个班级表，包含班级名和班级所在的教室

create table myclass(
class_name varchar(20) not null,
class_room varchar(10) not null);

MariaDB [test]> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

//插入数据时,没有给教室数据插入失败
MariaDB [test]> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

默认值

某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选
择性的使用默认值。

mysql> create table tt10 (
-> name varchar(20) not null,
-> age tinyint unsigned default 0,
-> sex char(2) default '男'
-> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> desc tt10
    -> ;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+

//默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
insert into tt10(name)  values('小栗');
MariaDB [test]> select * from tt10;
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| 小栗   |    0 | 男   |
+--------+------+------+

列描述

列描述：comment，没有实际含义，专门用来描述字段，相当于注释。

MariaDB [test]> create table tt12 (name varchar(20) not null comment '姓名',
-> age tinyint unsigned default 0 comment '年龄',sex char(2) default '男' comment '性别');

MariaDB [test]> desc tt12;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+

MariaDB [test]> show create table tt12\G
*************************** 1. row ***************************
       Table: tt12
Create Table: CREATE TABLE `tt12` (
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

UNIQUE 唯一键

保证某列的每行必须有唯一的值，即对于添加了唯一约束的数据项不能有重复

//对id添加unique约束
create table student(
	id int UNIQUE,
	age int,
	name varchar(8),
	birth date,
	math decimal(10, 2),
	english decimal(10, 2)
);

//插入第一条数据
INSERT INTO student VALUES(1, 11, "李华", 20200812, 76.5, 87.5);

Query OK, 1 row affected (0.002 sec)//插入成功

//插入第二条数据，除了id与第一条相同，其他全都不同
INSERT INTO student VALUES(1, 15, "李梅", 20200813, 88.5, 90.5);

ERROR 1062 (23000): Duplicate entry '1' for key 'id'//报错，id必须唯一，不能重复

主键

primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主
键所在的列通常是整数类型。

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键的特性即非空且唯一，如果在没有指定主键的时候，如果某一列具有非空且唯一的特性，他就会被暂定为主键，但是主键只能有一个。

//id为非空且唯一
create table student(
	id int NOT NULL UNIQUE,
	age int,
	name varchar(8),
	birth date,
	math decimal(10, 2),
	english decimal(10, 2)
);

desc student;//可以看到id成为了主键
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| age     | int(11)       | YES  |     | NULL    |       |
| name    | varchar(8)    | YES  |     | NULL    |       |
| birth   | date          | YES  |     | NULL    |       |
| math    | decimal(10,2) | YES  |     | NULL    |       |
| english | decimal(10,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

//使用PRIMARY KEY
DROP TABLE student;
create table student(
	id int PRIMARY KEY,
	age int,
	name varchar(8),
	birth date,
	math decimal(10, 2),
	english decimal(10, 2)
);
DESC student;

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| age     | int(11)       | YES  |     | NULL    |       |
| name    | varchar(8)    | YES  |     | NULL    |       |
| birth   | date          | YES  |     | NULL    |       |
| math    | decimal(10,2) | YES  |     | NULL    |       |
| english | decimal(10,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

主键只能有一个，而非空不唯一可以有多个，如果有多个非空不唯一，则只会有第一个是主键

如果想要使用多个列共同作为主键，就得使用下面这种语法

//在末尾表明组合主键的列有哪些
create table student(
	id int,
	age int,
	name varchar(8),
	birth date,
	math decimal(10, 2),
	english decimal(10, 2),
	PRIMARY KEY(id, name)
);

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| age     | int(11)       | YES  |     | NULL    |       |
| name    | varchar(8)    | NO   | PRI | NULL    |       |
| birth   | date          | YES  |     | NULL    |       |
| math    | decimal(10,2) | YES  |     | NULL    |       |
| english | decimal(10,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

不能直接在多个列后面加上PRIMARY KEY,那样的意思是创建多个主键，但是主键是唯一的，所以会报错

//错误写法
create table student(
	id int PRIMARY KEY,
	age int,
	name varchar(8) PRIMARY KEY,
	birth date,
	math decimal(10, 2),
	english decimal(10, 2)
);

ERROR 1068 (42000): Multiple primary key defined//报错，定义了多个主键

当表创建好以后，可以再次追加主键

alter table 表名 add primary key(字段列表)

删除主键

alter table 表名 drop primary key;

AUTO_INCREMENT 自增

添加自增属性的项必须为数字，并且必须为主键，并且只有缺省的时候才会使用自增。

表中数据从1开始自增，每次为上一条记录的+1
如果删除了表中数据，序号并不会重置，而是继续从删除的位置自增

create table student(
	id int PRIMARY KEY AUTO_INCREMENT,
	age int,
	name varchar(8),
	birth date,
	math decimal(10, 2),
	english decimal(10, 2)
);

INSERT INTO student(age, name) values(14, "张三");
INSERT INTO student(age, name) values(16, "李四");

select * from student;
+----+------+--------+-------+------+---------+
| id | age  | name   | birth | math | english |
+----+------+--------+-------+------+---------+
|  1 |   14 | 张三   | NULL  | NULL |    NULL |
|  2 |   16 | 李四   | NULL  | NULL |    NULL |
+----+------+--------+-------+------+---------+

//接着删除前两条
DELETE FROM student WHERE id < 3;

INSERT INTO student(age, name) values(14, "张三");
INSERT INTO student(age, name) values(16, "李四");
INSERT INTO student(age, name) values(19, "王五");
INSERT INTO student(age, name) values(15, "马六");

+----+------+--------+-------+------+---------+
| id | age  | name   | birth | math | english |
+----+------+--------+-------+------+---------+
|  3 |   14 | 张三   | NULL  | NULL |    NULL |
|  4 |   16 | 李四   | NULL  | NULL |    NULL |
|  5 |   19 | 王五   | NULL  | NULL |    NULL |
|  6 |   15 | 马六   | NULL  | NULL |    NULL |
+----+------+--------+-------+------+---------+
//可以看到序号并没有被重置，而是从删除的地方继续自增

FOREIGN KEY（外键约束）

保证一个表中的数据匹配另一个表中的值的参照完整性。

当我们的表中有数据与另一个表有关联的时候，就需要用到外键约束。例如学生表中存储了班级的信息，但是在班级表中并没有这个班级存在，就会导致数据出现冲突，所以必须将两个表关联起来。

语法

FOREIGN KEY (外键项) REFERENCES 关联表名(关联表中的对应项)

create table classes(
	id int PRIMARY KEY AUTO_INCREMENT
);

create table student(
	id int PRIMARY KEY AUTO_INCREMENT,
	age int,
	name varchar(8),

	classid int,
	FOREIGN KEY (classid) REFERENCES classes(id)
);

//添加班级
insert into classes values(1);
insert into classes values(2);

//插入学生信息
//插入成功
INSERT INTO student(name, classid) VALUES("张三", 1);
INSERT INTO student(name, classid) VALUES("李四", 2);

//插入失败，3号班级并不存在
INSERT INTO student(name, classid) VALUES("王五", 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lee`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `classes` (`id`))