Introduction détaillée aux contraintes d'intégrité MySQL

1. Introduction

Les conditions de contrainte sont les mêmes que la largeur du type de données, et ce sont des paramètres facultatifs

Fonction : Utilisé pour garantir les données L'intégrité et la cohérence de

sont principalement divisées en :

PRIMARY KEY (PK) #Identifie ce champ comme la clé primaire de la table, qui peut de manière unique identifier l'enregistrement

FOREIGN KEY (FK) #Identifie ce champ comme une clé étrangère de la table

NOT NULL #Identifie que ce champ ne peut pas être vide

UNIQUE KEY (UK) #Identifie ce champ La valeur est unique

AUTO_INCREMENT #La valeur identifiant le champ est automatiquement augmentée (type entier et clé primaire)

DEFAULT #Définir une valeur par défaut pour le champ

UNSIGNED #Non signé

ZEROFILL #Utilisez 0 pour remplir

Description :

#1. S'il faut autoriser le vide, NULL par défaut, NOT NULL peut être défini, le champ ne peut pas être vide et doit se voir attribuer une valeur

#2. la valeur par défaut est NULL, si aucune valeur n'est attribuée au champ lors de l'insertion de l'enregistrement, ce champ utilise Valeur par défaut

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

#doit être une valeur positive (non signée), ne peut pas être vide, la valeur par défaut est 20age int unsigned NOT NULL default 20

# 3. Qu'il s'agisse d'une clé

primaire clé clé primaire

clé étrangère clé étrangère

index (index, unique...)

2. 🎜> est nullable, null signifie vide, non-string

not null - not null

null - nullable

Valeur par défaut, vous pouvez spécifier la valeur par défaut lors de la création d'un colonne, si elle n'est pas activement définie lors de l'insertion des données, la valeur par défaut sera automatiquement ajoutée

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

Remarque :

La valeur par défaut peut être vide<🎜. >

2. Définissez non nul, et il ne peut pas être vide lors de l'insertion de la valeur

3 Après avoir défini le champ d'identification pour avoir une valeur par défaut, peu importe que le champ d'identification soit nul ou non, vous pouvez insérer des valeurs nulles Lors de l'insertion de valeurs nulles, la valeur par défaut spécifiée par défaut est renseignée

3. UNIQUE

Traduction chinoise : différente. C'est ce qu'on appelle une colonne unique unique dans MySQL

Exemple : Créer une table de département d'entreprise (chaque entreprise a un département unique)

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

mysql> insert into department values(1,'IT'),(2,'IT');
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 解决这种不合理的现象。

Ensuite, utilisez la contrainte unique pour définir les champs de département d'entreprise à définir .

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

Joint unique :

# 创建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,'192,168,11,23',80),
       (2,'192,168,11,23',81),
       (3,'192,168,11,25',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,'192,168,11,23',80);
ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

4. CLÉ PRIMAIRE

Il n'y a qu'une seule unique dans une table. dans MySQL Une clé primaire ne peut pas avoir plusieurs colonnes, mais elle peut avoir une clé primaire composite

Une table peut avoir :

Une seule colonne comme clé primaire

Plusieurs colonnes comme clé primaire key (clé primaire composite)

Contrainte : Equivalent à non null unique, la valeur du champ n'est pas vide et unique

Le moteur de stockage par défaut est (innodb) : Pour le moteur de stockage innodb , une table doit avoir une clé primaire.

Clé primaire à colonne unique :

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

mysql> insert into t14 values(2,'wxxx');
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
#   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)

Clé primaire composée :

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

AUTO_INCREMENT

Contraintes : Les champs contraints grandissent automatiquement. Les champs contraints doivent être contraints par clé en même temps

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

1 Si l'identifiant n'est pas spécifié, il grandira automatiquement

2. . Vous pouvez également spécifier l'identifiant

3 Pour un champ auto-croissant, après l'avoir supprimé avec delete puis inséré la valeur, le champ continuera à s'agrandir en fonction de la position avant la suppression

<🎜. >La différence entre

et

查看可用的 开头auto_inc的词
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| 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'auto_inc%';
发现跟之前一样，必须先exit,再登录才有效。

mysql> show variables like'auto_inc%';
+--------------------------+-------+
| 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('ma1'),('ma2'),('ma3');
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

Effacer le tableau pour distinguer la différence entre auto_increment_increment et auto_increment_offset :

supprimer de t1 #S'il y a un auto ; -incrémentation de l'identifiant, les données nouvellement ajoutées commenceront toujours par la dernière avant la suppression. deletetruncatetronquer la table t1 ; La quantité de données est importante, la vitesse de suppression est plus rapide que la précédente et elle repart directement de zéro.

6. CLÉ ÉTRANGÈRE

L'entreprise compte 3 départements, mais il y a 100 millions d'employés, ce qui signifie que le champ du département doit être stocké à plusieurs reprises. Plus le nom du département est long, plus il y a de gaspillage. c'est.

À ce stade,

solution :

Nous pouvons définir complètement une table de département

puis laisser la table d'informations sur les employés s'associer à la table, comment à associer, c'est une clé étrangère

Créez deux tables :

#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,'IT','IT技术有限部门'),
(2,'销售部','销售部门'),
(3,'财务部','花钱太多部门');
insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'djb',20,2),
(4,'dogfa',40,3),
(5,'oldniu',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)

L'opération ci-dessus de suppression des enregistrements de table est relativement lourde, logiquement parlant, si un service est licencié, les employés de ce service. sera également licencié. En fait, il existe un autre contenu très important lors de la création d'une table, appelé suppression synchrone et mise à jour synchrone

Ensuite, supprimez toutes les deux tables que vous venez de créer, supprimez d'abord la table associée (emp), puis supprimez. la table associée (dep)

Suivant :

Répétez l'opération ci-dessus pour créer la table

Remarque

: Ajoutez

à la table associée # Suppression synchrone #Mise à jour synchrone
on delete cascade
Modifier la table emp : on update cascade

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视频教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!