Résumé d'apprentissage Mariadb (5) : contraintes des tables de base de données et trois paradigmes

Trois paradigmes majeurs de bases de données

Forme normale (NF) : lors de la conception d'une base de données relationnelle, suivez différentes exigences normatives pour concevoir une base de données relationnelle raisonnable. Ces différentes exigences normatives sont appelées différents paradigmes. Différents paradigmes sont présentés dans différentes spécifications. Plus la redondance de la base de données paradigmatique est élevée, plus I. plus je deviens petit. Cependant, parfois, la poursuite aveugle de paradigmes visant à réduire la redondance réduira en fait l'efficacité de la lecture et de l'écriture des données. À l'heure actuelle, il est nécessaire d'inverser le paradigme et d'utiliser l'espace pour échanger du temps. Il peut être grossièrement compris comme le niveau d'une certaine norme de conception à laquelle se conforme la structure d'une table de données.

1NF C'est-à-dire que les colonnes de la table sont atomiques et ne peuvent pas être décomposées, c'est-à-dire que les informations des colonnes ne peuvent pas être décomposées tant que la base de données est une base de données relationnelle (mysql/oracle/db2/informix/sysbase/sql server), il satisfait automatiquement à 1NF. Chaque colonne d'une table de base de données est un élément de données atomiques indivisible et ne peut pas être une collection, un tableau, un enregistrement ou d'autres éléments de données non atomiques. Si un attribut d'une entité a plusieurs valeurs, il doit être divisé en différents attributs. L’idée répandue est qu’un champ ne stocke qu’une seule information.

Ce qui précède n'est pas conforme au premier paradigme, car les achats et les ventes peuvent être divisés en quantité achetée, unité d'achat, unité de vente, quantité vendue, etc. Ce qui suit répond au premier paradigme.

2NF La deuxième forme normale (2NF) est établie sur la base de la première forme normale (1NF), c'est-à-dire que pour satisfaire la deuxième forme normale (2NF), la première forme normale (1NF) doit d'abord être satisfaite. Après avoir satisfait 1NF, il est nécessaire que toutes les colonnes de la table dépendent de la clé primaire, et il ne peut y avoir aucune colonne qui n'ait aucune relation avec la clé primaire, ce qui signifie qu'une table ne décrit qu'une seule chose

 ;

Par exemple : la table de commande décrit uniquement les informations relatives à la commande, donc tous les champs doivent être liés à l'identifiant de la commande ; la table des produits décrit uniquement les informations relatives au produit, donc tous les champs doivent être liés à l'identifiant du produit, donc les informations sur la commande ; ne peut pas apparaître dans un seul tableau en même temps et dans les informations sur le produit comme indiqué ci-dessous :

3NF Doit d'abord satisfaire à la deuxième forme normale (2NF), qui requiert : Chaque colonne du tableau est uniquement directement liée à la clé primaire et non indirectement liée. Chaque colonne du tableau ne peut dépendre que de la clé primaire.

Par exemple : la table de commande doit contenir des informations relatives au client. Une fois la table client séparée, la table de commande n'a besoin que d'un seul identifiant utilisateur et aucune autre information client. Parce que les autres informations client sont directement liées à l'ID utilisateur, et non directement à l'ID de commande.

Contraintes diverses

Les contraintes sont un ensemble de règles utilisées pour limiter l'exactitude, l'exhaustivité, la cohérence et le couplage des données dans un tableau. Dans Mysql, les contraintes sont stockées dans table_constraints de la base de données information_schema et les informations sur les contraintes peuvent être interrogées via cette table. Comme indiqué ci-dessous :

PAS NULL

Contrainte non nulle, si la valeur de cette colonne peut être NULL, une chose est très importante ici, la valeur par défaut de nombreux champs (sauf l'heure ?) est NULL si elle n'est pas spécifiée, donc sauf NULL=NULL, autres valeurs ​​ne sont pas égaux à NULL Comme "", 0, etc.

Modifier un champ en NON NULL :

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)   
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | NO   |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

Il y a un autre problème ici, car la valeur par défaut est NULL mais ce champ n'est pas spécifié pour être inséré :

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
//看username这个字段，默认值为NULL，不允许NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
//这里看到我们插入成功了。
MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 |          | test7    | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL  |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

Vous pouvez voir que la valeur de la colonne nom d'utilisateur est un caractère nul et que sa valeur par défaut est NULL,
La valeur par défaut de logip est NULL, mais les valeurs NULL peuvent être insérées, donc les valeurs NULL sont affichées ici.

Vérifiez-le ~ Parce que NULL est la valeur par défaut, mais les valeurs NULL ne sont pas autorisées, cela signifie que le champ du nom d'utilisateur n'a plus de valeur. En raison de SQL_MODE, il donnera uniquement un avertissement et ne signalera pas directement une erreur. nous spécifions SQL_MODE comme 'STRICT_ALL_TABLES', l'erreur suivante sera signalée lors de l'insertion :

MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

UNIQUE

unique représente une contrainte unique : une contrainte unique signifie que les colonnes ou les combinaisons de colonnes de la table spécifiée ne peuvent pas être répétées pour garantir l'unicité des données. Bien que la contrainte unique n'autorise pas les valeurs en double, peut être plusieurs valeurs nulles, et. une même table peut avoir plusieurs Une contrainte unique, une contrainte combinant plusieurs colonnes. Lors de la création d'une contrainte unique, si vous ne donnez pas de nom de contrainte unique, il sera par défaut identique au nom de la colonne et MySQL créera un index unique par défaut sur la colonne de la contrainte unique.

Ajouter une contrainte unique :

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username为约束名称，UNIQUE(可多个字段）

//当插入用户名相同的数据事则会直接报错

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'

//删除此约束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;

//添加两个字段的约束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);

//测试添加数据
MariaDB [mydb]> SELECT * FROM user;                                                     
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
|  7 | test2    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  8 | test3    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  9 | test4    | test5    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)

//仅当两个字段的数据都相同时才违反唯一约束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

CLÉ PRIMAIRE

La contrainte de clé primaire est équivalente à la combinaison de contrainte unique + contrainte non nulle La colonne de contrainte de clé primaire n'autorise pas la duplication ou les valeurs nulles. S'il s'agit d'une contrainte de clé primaire qui combine plusieurs colonnes, aucune de ces colonnes ne peut avoir de valeurs nulles et les valeurs combinées ne peuvent pas être répétées. Chaque table n'autorise qu'une seule clé primaire au maximum. La contrainte de clé primaire peut être créée au niveau de la colonne ou au niveau de la table. Le nom de clé primaire de MySQL est toujours PRIMARY. Lors de la création d'une contrainte de clé primaire, le système utilise par défaut le nom de clé primaire. colonne et créez un index unique correspondant sur la combinaison de colonnes.

Le fonctionnement est le suivant :

//因为现在的表中已经有主键了，先把主键删掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

//告诉我们一张表里只允许有一个字段为自动增长，且这个字段必须是主键，所以，我们要先取消它的自动增长。

MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field    | Type        | Null | Key | Default             | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id       | int(11)     | NO   | PRI | NULL                |       |


//再次删除主键
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)               
Records: 4  Duplicates: 0  Warnings: 0

//好了，再让我们把主键加上吧~~~   以下两种方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;

CLÉ ÉTRANGÈRE

Les contraintes de clés étrangères assurent l'intégrité référentielle entre une ou deux tables Les clés étrangères sont construites sur la relation de référence entre deux champs d'une table ou deux champs de deux tables . C'est-à-dire que la valeur de clé étrangère de la table esclave doit être trouvée dans la table maître ou être vide. Lorsque les enregistrements de la table maître sont référencés par la table esclave, les enregistrements de la table maître ne seront pas supprimés. Si vous souhaitez supprimer les données, vous devez d'abord supprimer la table esclave. Les données de la table dépendent de l'enregistrement, puis les données de la table principale peuvent être supprimées. Une autre façon consiste à supprimer en cascade les données de la sous-table. tableau. Remarque : La colonne de référence d'une contrainte de clé étrangère ne peut faire référence qu'à la colonne de la contrainte de clé primaire ou de clé unique dans la table principale. En supposant que la colonne de la table principale référencée n'est pas le seul enregistrement, les données référencées à partir de la table le seront. ne soyez pas sûr de l'emplacement de l'enregistrement. La même table peut avoir plusieurs contraintes de clé étrangère.
Maintenant, créons une table GROUP pour enregistrer les informations de groupe de l'utilisateur,

 CREATE TABLE `usergroup` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `comment` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |

Ensuite~ ajoutez un enregistrement à la table des utilisateurs pour enregistrer à quel groupe appartient l'utilisateur

MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

//Ajouter une clé étrangère

ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);

//Vérifier les contraintes de clé étrangère

MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))

//Il peut être vide, mais il ne peut pas s'agir d'une valeur qui n'est pas dans le tableau de référence

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)

Définition de clé étrangère :

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Les opérations en cascade suivantes nécessitent une attention particulière :

ON DELETE CASCADE : lors de la suppression d'une ligne dans la table parent (de référence), s'il y a des lignes enfants dans la table enfant qui dépendent de la ligne parent supprimée, les lignes enfants seront supprimées ensemble. Ce n'est pas recommandé.

ON DELETE SET NULL : lors de la suppression d'une ligne dans la table parent (référence), s'il y a une ligne enfant dans la table enfant qui dépend de la ligne parent supprimée, alors elle ne sera pas supprimée, mais la colonne de clé étrangère de l'enfant la ligne sera définie sur NULL

VÉRIFIER La contrainte CHECK consiste à effectuer une vérification de contrainte CHECK lors de l'insertion d'une ligne ou de la mise à jour d'une ligne de données dans la table. CHECK accepte une expression si l'expression est VRAIE, l'insertion est autorisée. Si l'expression est FAUX, l'insertion est rejetée. Dans la version MariaDB10.2, la prise en charge de CHECK a seulement commencé.

Les contraintes CHECK courantes incluent :

CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date 
<p>Exemple : Vérifiez si la longueur du nom d'utilisateur est supérieure à 0</p>
<pre class="brush:php;toolbar:false">
ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL错误（4025）：CONSTRAINT `non_empty_name` failed for `test`.`user` */

Cette chose semble très inutile. Il semble que le jugement des données soit généralement effectué au niveau de la couche métier et que la base de données n'a besoin que de stocker les données.

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!