집 > 기사 > 데이터 베이스 > MySQL 무결성 제약 조건에 대한 자세한 소개

MySQL 무결성 제약 조건에 대한 자세한 소개

王林앞으로: 2019-08-28 14:06:092734검색

1. 소개

제약 조건은 데이터 유형의 너비와 같은 선택적 매개 변수입니다.

기능: 데이터의 무결성과 일관성을 보장하는 데 사용됩니다.

주로 구분:

PRIMARY KEY code> (PK) #이 필드를 레코드를 고유하게 식별할 수 있는 테이블의 기본 키로 식별합니다<code>PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK) #标识该字段为该表的外键

NOT NULL #标识该字段不能为空

UNIQUE KEY (UK) #标识该字段的值是唯一的

AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT #为该字段设置默认值

UNSIGNED #无符号

ZEROFILL #使用0填充

说明：

#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值

#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'

#必须为正值（无符号）不允许为空默认是20age int unsigned NOT NULL default 20

# 3. 是否是key

主键 primary key

外键 foreign key

索引 (index,unique...)

二、NOT NULL 和DEFAULT

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table tb1(
    nid int not null defalut 2,    
    num int not null);

注意：

1、默认值可以为空

2、设置not null，插入值时不能为空

3、设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

三、UNIQUE

中文翻译：不同的。在mysql中称为单列唯一

举例说明：创建公司部门表（每个公司都有唯一的一个部门）

mysql> create table department(
    -> id int,
    -> name char(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into department values(1,&#39;IT&#39;),(2,&#39;IT&#39;);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from department;
+------+------+| id   | name |
+------+------+|    1 | IT   |
|    2 | IT   |
+------+------+2 rows in set (0.00 sec)
# 发现： 同时插入两个IT部门也是可以的，但这是不合理的，所以我们要设置name字段为unique 解决这种不合理的现象。

接下来，使用约束条件unique，来对公司部门的字段进行设置。

#第一种创建unique的方式#例子1：create table department(
    id int,
    name char(10) unique
);
mysql> insert into department values(1,&#39;it&#39;),(2,&#39;it&#39;);
ERROR 1062 (23000): Duplicate entry &#39;it&#39; for key &#39;name&#39;
#例子2：create table department(
    id int unique,
    name char(10) unique
);
insert into department values(1,&#39;it&#39;),(2,&#39;sale&#39;);
#第二种创建unique的方式create table department(
    id int,
    name char(10) ,    unique(id),    unique(name)
);
insert into department values(1,&#39;it&#39;),(2,&#39;sale&#39;);

联合唯一：

# 创建services表mysql> create table services(
        id int,
        ip char(15),
        port int,
        unique(id),
        unique(ip,port)
       );
Query OK, 0 rows affected (0.05 sec)

mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id        | int(11)   | YES   | UNI  | NULL       |             
| ip        | char(15) | YES   | MUL  | NULL       |          
| port    | int(11) | YES   |          | NULL       |             
|+-------+----------+------+-----+---------+-------+|
3 rows in set (0.01 sec)
#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束mysql> insert into services values
       (1,&#39;192,168,11,23&#39;,80),
       (2,&#39;192,168,11,23&#39;,81),
       (3,&#39;192,168,11,25&#39;,80);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192,168,11,23 |   80 |
|    2 | 192,168,11,23 |   81 |
|    3 | 192,168,11,25 |   80 |
+------+---------------+------+
3 rows in set (0.00 sec)

mysql> insert into services values (4,&#39;192,168,11,23&#39;,80);
ERROR 1062 (23000): Duplicate entry &#39;192,168,11,23-80&#39; for key &#39;ip&#39;

四、PRIMARY KEY

在MySQL的一个表中只有唯一的一个主键，不能有多列主键，但可以有复合主键

一个表中可以：

单列做主键
多列做主键（复合主键）

约束：等价于 not null unique,字段的值不为空且唯一

存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。

单列主键：

# 创建t14表，为id字段设置主键，唯一的不同的记录create table t14(
    id int primary key,
    name char(16)
);
insert into t14 values(1,&#39;xiaoma&#39;),(2,&#39;xiaohong&#39;);

mysql> insert into t14 values(2,&#39;wxxx&#39;);
ERROR 1062 (23000): Duplicate entry &#39;6&#39; for key &#39;PRIMARY&#39;
#   not null + unique的化学反应，相当于给id设置primary key
create table t15(
    id int not null unique,
    name char(16)
);
mysql> create table t15(
    -> id int not null unique,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id        | int(11)  | NO     | PRI | NULL       |             |
| name   | char(16) | YES  |         | NULL       |             |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)

复合主键：

create table t16(
    ip char(15),
    port int,
    primary key(ip,port)
);
insert into t16 values(&#39;1.1.1.2&#39;,80),(&#39;1.1.1.2&#39;,81);

五、AUTO_INCREMENT

约束:约束的字段为自动增长，约束的字段必须同时被key约束

create table student(
id int primary key auto_increment,
name varchar(20),
sex enum(&#39;male&#39;,&#39;female&#39;) default &#39;male&#39;
);

1、不指定id，则自动增长

2、也可以指定id

3、对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

auto_increment_increment和 auto_increment_offset的区别

查看可用的 开头auto_inc的词
mysql> show variables like &#39;auto_inc%&#39;;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
rows in set (0.02 sec)
# 步长auto_increment_increment,默认为1
# 起始的偏移量auto_increment_offset, 默认是1

 # 设置步长 为会话设置，只在本次连接中有效
 set session auto_increment_increment=5;

 #全局设置步长 都有效。
 set global auto_increment_increment=5;

 # 设置起始偏移量
 set global  auto_increment_offset=3;

#强调：If the value of auto_increment_offset is greater than that of auto_increment_increment, 
the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 

# 设置完起始偏移量和步长之后，再次执行show variables like&#39;auto_inc%&#39;;
发现跟之前一样，必须先exit,再登录才有效。

mysql> show variables like&#39;auto_inc%&#39;;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 3     |
+--------------------------+-------+
rows in set (0.00 sec)

#因为之前有一条记录id=1
mysql> select * from student;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec)
# 下次插入的时候，从起始位置3开始，每次插入记录id+5
mysql> insert into student(name) values(&#39;ma1&#39;),(&#39;ma2&#39;),(&#39;ma3&#39;);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from student;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | xiaobai | male |
|  3 | ma1     | male |
|  8 | ma2     | male |
| 13 | ma3     | male |
+----+---------+------+
auto_increment_increment和 auto_increment_offset

清空表区分delete和truncate的区别：

delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。

六、FOREIGN KEY

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。

这个时候，

解决方法：

我们完全可以定义一个部门表

然后让员工信息表关联该表，如何关联，即foreign key

创建两张表操作：

#1.创建表时先创建被关联表，再创建关联表
# 先创建被关联表（dep表）
create table dep(
    id int primary key,
    name varchar(20) not null,
    descripe varchar(20) not null
);
#再创建关联表（emp表）
create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
);
#2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录
insert into dep values
(1,&#39;IT&#39;,&#39;IT技术有限部门&#39;),
(2,&#39;销售部&#39;,&#39;销售部门&#39;),
(3,&#39;财务部&#39;,&#39;花钱太多部门&#39;);
insert into emp values
(1,&#39;zhangsan&#39;,18,1),
(2,&#39;lisi&#39;,19,1),
(3,&#39;djb&#39;,20,2),
(4,&#39;dogfa&#39;,40,3),
(5,&#39;oldniu&#39;,18,2);
3.删除表
#按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除。
mysql> delete from dep where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
(`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
#但是先删除员工表的记录之后，再删除当前部门就没有任何问题
mysql> delete from emp where dep =3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name     | age | dep_id |
+----+----------+-----+--------+
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  18 |      1 |
|  3 | djb      |  20 |      2 |
|  5 | oldniu   |  18 |      2 |
+----+----------+-----+--------+
rows in set (0.00 sec)
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep;
+----+-----------+----------------------+
| id | name      | descripe             |
+----+-----------+----------------------+
|  1 | IT        | IT技术有限部门       |
|  2 | 销售部    | 销售部门             |
+----+-----------+----------------------+
rows in set (0.00 sec)

上面的删除表记录的操作比较繁琐，按道理讲，裁掉一个部门，该部门的员工也会被裁掉。其实呢，在建表的时候还有个很重要的内容，叫同步删除，同步更新

接下来将刚建好的两张表全部删除，先删除关联表（emp）,再删除被关联表（dep）

接下来：
重复上面的操作建表
注意：在关联表中加入
on delete cascade #同步删除
on update cascade

FOREIGN KEY (FK) #이 필드를 테이블의 외래 키로 식별합니다

NOT NULL #필드가 비어 있을 수 없음을 나타냅니다

UNIQUE KEY (UK) #이 필드의 값이 고유함을 나타냅니다🎜🎜AUTO_INCREMENT # 필드를 식별하는 값이 자동으로 증가합니다(정수 유형 및 기본 키)🎜🎜DEFAULT # 이 필드의 기본값을 설정합니다🎜🎜UNSIGNED #Unsigned🎜🎜ZEROFILL #Fill with 0🎜🎜Instructions:🎜🎜🎜#1. null 허용 여부, 기본값은 NULL, NOT NULL을 설정할 수 있으며 필드는 허용되지 않습니다. null이 허용됨, 값을 할당해야 함 🎜🎜 #2. 필드에 기본값이 있는지 여부. 레코드를 삽입할 때 필드에 값이 할당되지 않으면 필드는 기본값🎜🎜을 사용합니다. sex enum('male','female') not null 기본값 'male' 🎜🎜 #양수 값이어야 함(부호 없음), 비워둘 수 없음, 기본값은 20age int unsigned NOT NULL 기본값 20 🎜🎜# 3. 키 🎜🎜기본 키 🎜🎜외래 키 외래 키 🎜🎜index(index, Unique ...)🎜🎜🎜🎜2. NOT NULL 및 DEFAULT🎜🎜🎜🎜가 null인지 여부, null은 비어 있고 문자열이 아님을 의미합니다. 🎜not null - not nullable🎜null - nullable🎜🎜열 생성 시 지정할 수 있는 기본값 기본값, 데이터 삽입 시 적극적으로 설정되지 않으면 기본값이 자동으로 추가됩니다🎜

create table emp(    
    id int primary key,    
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
    on delete cascade #同步删除    
    on update cascade #同步更新
);

🎜🎜참고:🎜 🎜🎜1. 기본값은 비어 있을 수 있습니다🎜🎜2. null이 아닌 것으로 설정하면 id 필드가 null인지 여부에 관계없이 id 필드를 기본값으로 설정한 후 값을 삽입할 때 비어 있을 수 없습니다. null이 아닌 경우 공백을 삽입할 수 있습니다. 공백을 삽입하면 기본적으로 지정된 기본값이 기본적으로 채워집니다.🎜🎜🎜🎜Three, UNIQUE🎜🎜🎜🎜중국어 번역: 다름. mysql에서는 단일 열 고유라고 합니다🎜🎜예: 회사 부서 테이블 만들기(각 회사마다 고유한 부서가 있음)🎜

#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep;
+----+-----------+----------------------+
| id | name      | descripe             |
+----+-----------+----------------------+
|  1 | IT        | IT技术有限部门       |
|  2 | 销售部    | 销售部门             |
+----+-----------+----------------------+
rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name     | age | dep_id |
+----+----------+-----+--------+
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | djb      |  20 |      2 |
|  5 | oldniu   |  18 |      2 |
+----+----------+-----+--------+
rows in set (0.00 sec)

🎜다음으로 고유 제약 조건을 사용하여 회사 부서의 필드를 설정합니다. 🎜

 mysql> update dep set id=222 where id=2; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

🎜United 고유:🎜

# 赶紧去查看一下两张表是否都被删除了，是否都被更改了
mysql> select * from dep;
+-----+-----------+----------------------+
| id  | name      | descripe             |
+-----+-----------+----------------------+
|   1 | IT        | IT技术有限部门       |
| 222 | 销售部    | 销售部门             |
+-----+-----------+----------------------+
rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name     | age | dep_id |
+----+----------+-----+--------+
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | djb      |  20 |    222 |
|  5 | oldniu   |  18 |    222 |
+----+----------+-----+--------+
rows in set (0.00 sec)

🎜🎜🎜4. PRIMARY KEY🎜🎜🎜🎜🎜MySQL 테이블에는 기본 키가 하나만 있을 수 있지만, 복합 기본 키는 있을 수 있습니다.🎜🎜A 테이블. 가능: 🎜🎜기본 키로 단일 열🎜기본 키로 여러 열(복합 기본 키)🎜🎜제약 조건: null이 아닌 고유와 동일하며 필드 값은 비어 있지 않고 고유합니다.🎜🎜기본 스토리지 엔진은 (innodb)입니다. : innodb 스토리지 엔진의 경우 테이블에는 기본 키가 있어야 합니다. 🎜🎜단일 열 기본 키: 🎜rrreee🎜복합 기본 키: 🎜rrreee🎜🎜🎜 5. AUTO_INCREMENT🎜🎜🎜🎜🎜제약: 제한된 필드는 자동으로 커지며, 제한된 필드는 동시에 키로 제한되어야 합니다🎜 rrreee🎜1. ID가 지정되지 않으면 자동으로 커집니다🎜🎜2. ID를 지정할 수도 있습니다🎜🎜3. 자동 증가 필드의 경우 삭제 후 값을 삽입하면 삭제 전 위치에 따라 계속 성장🎜🎜auto_increment_incrementauto_increment_offset의 차이점🎜rrreee🎜테이블을 지우고 delete와 를 구분하세요 >truncate: 🎜🎜delete from t1; #자동 증가 ID가 있는 경우 새로 추가된 데이터는 삭제 전 마지막 데이터부터 시작됩니다. 🎜🎜truncate table t1; 데이터 볼륨이 크고 삭제 속도가 이전보다 빠르며 0부터 바로 시작됩니다. 🎜🎜6. FOREIGN KEY🎜🎜회사에는 부서가 3개 있지만 직원이 1억 명이므로 부서 필드를 반복해서 저장해야 한다는 의미입니다. 🎜🎜이때 🎜🎜해결 방법: 🎜🎜부서 테이블을 완전히 정의한 다음🎜🎜직원 정보 테이블을 테이블과 연결하는 방법, 즉 외래 키🎜🎜두 개의 테이블을 만듭니다. 🎜rrreee🎜위 삭제 테이블 녹화 작업은 논리적으로 보면 부서가 해고되면 해당 부서의 직원도 해고됩니다. 사실, 테이블 생성 시 또 하나의 매우 중요한 내용이 있는데 바로 동기삭제, 동기업데이트🎜🎜 다음으로 새로 생성된 두 개의 테이블을 모두 삭제한 뒤, 연관 테이블(emp)을 삭제합니다. (dep)🎜🎜다음:🎜위 작업을 반복하여 테이블을 생성합니다🎜🎜참고🎜: 연결 테이블에 🎜on delete cascade를 추가합니다. #Synchronous delete🎜on update cascade #동기화 업데이트🎜🎜🎜emp 테이블 수정:🎜🎜

create table emp(    
    id int primary key,    
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
    on delete cascade #同步删除    
    on update cascade #同步更新
);

接下来的操作，就符合我们正常的生活中的情况了。

#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep;
+----+-----------+----------------------+
| id | name      | descripe             |
+----+-----------+----------------------+
|  1 | IT        | IT技术有限部门       |
|  2 | 销售部    | 销售部门             |
+----+-----------+----------------------+
rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name     | age | dep_id |
+----+----------+-----+--------+
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | djb      |  20 |      2 |
|  5 | oldniu   |  18 |      2 |
+----+----------+-----+--------+
rows in set (0.00 sec)

#再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改

 mysql> update dep set id=222 where id=2; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 赶紧去查看一下两张表是否都被删除了，是否都被更改了
mysql> select * from dep;
+-----+-----------+----------------------+
| id  | name      | descripe             |
+-----+-----------+----------------------+
|   1 | IT        | IT技术有限部门       |
| 222 | 销售部    | 销售部门             |
+-----+-----------+----------------------+
rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name     | age | dep_id |
+----+----------+-----+--------+
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | djb      |  20 |    222 |
|  5 | oldniu   |  18 |    222 |
+----+----------+-----+--------+
rows in set (0.00 sec)

以上便是常用约束的详细实例介绍，想了解更多相关问题请访问PHP中文网：mysql视频教程

위 내용은 MySQL 무결성 제약 조건에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

php mysql 数据类型 NULL enum 字符串 int 整数类型 delete default table

성명：

이 기사는 cnblogs.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

이전 기사：MySQL - 다중 테이블 쿼리에 대한 자세한 소개 및 예다음 기사：MySQL - 다중 테이블 쿼리에 대한 자세한 소개 및 예