Zusammenfassung des Mariadb-Lernens (5): Datenbanktabellenbeschränkungen und drei Paradigmen

Drei große Datenbankparadigmen

Normale Form (NF): Befolgen Sie beim Entwurf einer relationalen Datenbank unterschiedliche normative Anforderungen, um eine angemessene relationale Datenbank zu entwerfen Je kleiner ich werde, desto kleiner werde ich. Manchmal führt die blinde Verfolgung von Paradigmen zur Redundanzreduzierung jedoch tatsächlich zu einer Verringerung der Effizienz beim Lesen und Schreiben von Daten. Zu diesem Zeitpunkt ist es notwendig, das Paradigma umzukehren und Platz zum Tauschen von Zeit zu nutzen. Es kann grob als die Ebene eines bestimmten Designstandards verstanden werden, dem die Tabellenstruktur einer Datentabelle entspricht.

1NF Das heißt, die Spalten der Tabelle sind atomar und können nicht zerlegt werden, das heißt, die Spalteninformationen können nicht zerlegt werden, solange die Datenbank eine relationale Datenbank ist (MySQL/Oracle/DB2/Informix/Sysbase/SQL-Server). es erfüllt automatisch 1NF. Jede Spalte einer Datenbanktabelle ist ein unteilbares atomares Datenelement und kann keine Sammlung, kein Array, kein Datensatz und kein anderes nichtatomares Datenelement sein. Wenn ein Attribut in einer Entität mehrere Werte hat, muss es in verschiedene Attribute aufgeteilt werden. Nach allgemeiner Auffassung speichert ein Feld nur eine Information.

Das Obige entspricht nicht dem ersten Paradigma, da Kauf und Verkauf weiter in Einkaufsmenge, Einkaufseinheit, Verkaufseinheit, Verkaufsmenge usw. unterteilt werden können. Das Folgende entspricht dem ersten Paradigma.

2NF Die zweite Normalform (2NF) wird auf der Grundlage der ersten Normalform (1NF) erstellt, d. h. um die zweite Normalform (2NF) zu erfüllen, muss zuerst die erste Normalform (1NF) erfüllt sein. Nach Erfüllung von 1NF müssen alle Spalten in der Tabelle vom Primärschlüssel abhängen und es darf keine Spalte geben, die keine Beziehung zum Primärschlüssel hat, was bedeutet, dass eine Tabelle nur eine Sache beschreibt

Zum Beispiel: Die Bestelltabelle beschreibt nur auftragsbezogene Informationen, daher müssen sich alle Felder auf die Bestell-ID beziehen. Die Produkttabelle beschreibt nur produktbezogene Informationen, daher müssen sich alle Felder auf die Produkt-ID beziehen können nicht gleichzeitig in einer Tabelle und Produktinformationen erscheinen, wie unten gezeigt:

3NF Muss zunächst die zweite Normalform (2NF) erfüllen, die Folgendes erfordert: Jede Spalte in der Tabelle steht nur in direktem Zusammenhang mit dem Primärschlüssel und nicht indirekt im Zusammenhang. Jede Spalte in der Tabelle kann nur vom Primärschlüssel abhängen.

Zum Beispiel: Die Bestelltabelle muss kundenbezogene Informationen enthalten. Nachdem die Kundentabelle getrennt wurde, muss die Bestelltabelle nur eine Benutzer-ID und keine anderen Kundeninformationen enthalten. Da andere Kundeninformationen direkt mit der Benutzer-ID und nicht direkt mit der Bestell-ID verknüpft sind.

Verschiedene Einschränkungen

Einschränkungen sind eine Reihe von Regeln, mit denen die Genauigkeit, Vollständigkeit, Konsistenz und Verknüpfung von Daten in einer Tabelle eingeschränkt werden. In MySQL werden Einschränkungen in table_constraints der Datenbank information_schema gespeichert, und Einschränkungsinformationen können über diese Tabelle abgefragt werden. Wie unten gezeigt:

NICHT NULL

Nicht-Null-Einschränkung, ob der Wert dieser Spalte NULL sein darf, eines ist hier sehr wichtig, der Standardwert vieler Felder (außer Zeit?) ist NULL, wenn nicht angegeben, also außer NULL=NULL, andere Werte ​​sind ungleich NULL, z. B. „“, 0 usw.

Ändern Sie ein Feld in NOT 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)

Hier gibt es ein weiteres Problem, denn der Standardwert ist NULL, aber dieses Feld ist nicht zum Einfügen angegeben:

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)

Sie können sehen, dass der Wert der Spalte „Benutzername“ ein Nullzeichen ist und der Standardwert NULL ist,
Der Standardwert von logip ist NULL, es dürfen jedoch NULL-Werte eingefügt werden, sodass hier NULL-Werte angezeigt werden.

Überprüfen Sie es ~ Da NULL der Standardwert ist, aber NULL-Werte nicht zulässig sind, bedeutet dies, dass das Feld „Benutzername“ jetzt keinen Wert hat. Aufgrund von SQL_MODE wird nur eine Warnung ausgegeben und kein Fehler direkt gemeldet Wenn wir SQL_MODE als „STRICT_ALL_TABLES“ angeben, wird beim Einfügen der folgende Fehler gemeldet:

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

EINZIGARTIG

unique stellt eine eindeutige Einschränkung dar: Eine eindeutige Einschränkung bedeutet, dass die Spalten oder Spaltenkombinationen der angegebenen Tabelle nicht wiederholt werden können, um die Eindeutigkeit der Daten sicherzustellen. Obwohl die eindeutige Einschränkung keine doppelten Werte zulässt, kann es sich um mehrere Nullen handeln Dieselbe Tabelle kann mehrere haben. Eine eindeutige Einschränkung, eine Einschränkung, die mehrere Spalten kombiniert. Wenn Sie beim Erstellen einer eindeutigen Einschränkung keinen eindeutigen Einschränkungsnamen angeben, wird dieser standardmäßig mit dem Spaltennamen identisch sein, und MySQL erstellt standardmäßig einen eindeutigen Index für die Spalte der eindeutigen Einschränkung.

Eindeutige Einschränkung hinzufügen:

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'

PRIMÄRSCHLÜSSEL

Die Primärschlüsseleinschränkung entspricht der Kombination aus

eindeutige Einschränkung + Nicht-Null-Einschränkung. Die Primärschlüsseleinschränkungsspalte lässt keine Duplizierung oder Nullwerte zu. Wenn es sich um eine Primärschlüsseleinschränkung handelt, die mehrere Spalten kombiniert, darf keine dieser Spalten Nullwerte haben und die kombinierten Werte dürfen nicht wiederholt werden. Jede Tabelle lässt höchstens einen Primärschlüssel zu. Der Primärschlüsselname von MySQL ist immer PRIMARY Spalte und erstellen Sie einen entsprechenden eindeutigen Index für die Spaltenkombination.

Die Funktionsweise ist wie folgt:

//因为现在的表中已经有主键了，先把主键删掉
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;

FÜLLIGER SCHLÜSSEL

Fremdschlüsseleinschränkungen

stellen die referenzielle Integrität zwischen einer oder zwei Tabellen sicher Fremdschlüssel basieren auf der Referenzbeziehung zwischen zwei Feldern einer Tabelle oder zwei Feldern zweier Tabellen . Das heißt, der Fremdschlüsselwert der Slave-Tabelle muss in der Master-Tabelle gefunden werden oder leer sein. Wenn die Datensätze der Master-Tabelle von der Slave-Tabelle referenziert werden, werden die Datensätze der Master-Tabelle nicht gelöscht Wenn Sie die Daten löschen möchten, müssen Sie zuerst die Daten in der Tabelle löschen. Anschließend können die Daten der Haupttabelle gelöscht werden. Eine andere Möglichkeit besteht darin, die Daten der Untertabelle zu löschen. Tisch. Hinweis: Die Referenzspalte einer Fremdschlüsseleinschränkung kann nur auf die Spalte des Primärschlüssels oder der Eindeutigkeitsschlüsseleinschränkung in der Haupttabelle verweisen. Unter der Annahme, dass die referenzierte Haupttabellenspalte nicht der einzige Datensatz ist, gilt dies für die in der Tabelle referenzierten Daten Seien Sie sich über den Speicherort des Datensatzes nicht sicher. Die gleiche Tabelle kann mehrere Fremdschlüsseleinschränkungen haben. Jetzt erstellen wir eine GROUP-Tabelle, um die Gruppeninformationen des Benutzers aufzuzeichnen,

 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 |

Fügen Sie dann einen Datensatz zur Benutzertabelle hinzu, um aufzuzeichnen, zu welcher Gruppe der Benutzer gehört

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

//Fremdschlüssel hinzufügen

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

//Fremdschlüsseleinschränkungen überprüfen

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`))

//Es kann leer sein, aber es darf kein Wert sein, der nicht in der Referenztabelle enthalten ist

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

Fremdschlüsseldefinition:

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

Die folgenden kaskadierenden Vorgänge erfordern Aufmerksamkeit:

ON DELETE CASCADE: Wenn beim Löschen einer Zeile in der übergeordneten (Referenz-)Tabelle untergeordnete Zeilen in der untergeordneten Tabelle vorhanden sind, die von der gelöschten übergeordneten Zeile abhängen, werden die untergeordneten Zeilen zusammen gelöscht.

ON DELETE SET NULL: Wenn beim Löschen einer Zeile in der übergeordneten (Referenz-)Tabelle eine untergeordnete Zeile in der untergeordneten Tabelle vorhanden ist, die von der gelöschten übergeordneten Zeile abhängt, wird diese nicht gelöscht, sondern die Fremdschlüsselspalte des untergeordneten Elements Zeile wird auf NULL

gesetzt

CHECK

CHECK-Einschränkung führt eine CHECK-Einschränkungsprüfung durch, wenn eine Zeile eingefügt oder eine Datenzeile in die Tabelle aktualisiert wird. Wenn der Ausdruck TRUE ist, ist die Einfügung zulässig. In der MariaDB10.2-Version wurde nur mit der Unterstützung von CHECK begonnen.

Zu den üblichen CHECK-Einschränkungen gehören:

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 
Beispiel: Überprüfen Sie, ob die Länge des Benutzernamens größer als 0 ist<p>
</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` */

Diese Sache sieht sehr nutzlos aus. Es scheint, dass die Datenbeurteilung normalerweise auf der Geschäftsebene erfolgt und die Datenbank nur die Daten speichern muss.

Das obige ist der detaillierte Inhalt vonZusammenfassung des Mariadb-Lernens (5): Datenbanktabellenbeschränkungen und drei Paradigmen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!