Ringkasan pembelajaran Mariadb (5): kekangan jadual pangkalan data dan tiga paradigma

Tiga paradigma pangkalan data utama

Borang Normal (NF): Apabila mereka bentuk pangkalan data relasi, ikuti keperluan normatif yang berbeza untuk mereka bentuk pangkalan data relasi yang munasabah ini dipanggil paradigma berbeza Pelbagai paradigma dibentangkan dalam spesifikasi yang berbeza dapatkan, semakin kecil saya. Walau bagaimanapun, kadangkala mengejar paradigma secara membuta tuli untuk mengurangkan redundansi sebenarnya akan mengurangkan kecekapan membaca dan menulis data Pada masa ini, adalah perlu untuk membalikkan paradigma dan menggunakan ruang untuk berdagang masa. Ia boleh difahami secara kasar sebagai tahap piawaian reka bentuk tertentu yang dipatuhi oleh struktur jadual jadual data.

1NF Iaitu, lajur jadual adalah atom dan tidak boleh diuraikan, iaitu, maklumat lajur tidak boleh diuraikan selagi pangkalan data adalah pangkalan data hubungan (mysql/oracle/db2/informix/sysbase/sql server), ia secara automatik memenuhi 1NF. Setiap lajur jadual pangkalan data ialah item data atom yang tidak boleh dibahagikan, dan tidak boleh menjadi koleksi, tatasusunan, rekod dan item data bukan atom yang lain. Jika atribut dalam entiti mempunyai berbilang nilai, ia mesti dibahagikan kepada atribut yang berbeza. Pemahaman popular adalah bahawa bidang hanya menyimpan satu maklumat.

Perkara di atas tidak menepati paradigma pertama, kerana pembelian dan jualan boleh dibahagikan lagi kepada kuantiti pembelian, unit pembelian, unit jualan, kuantiti jualan, dan lain-lain. Yang berikut memenuhi paradigma pertama.

2NF Bentuk normal kedua (2NF) diwujudkan berdasarkan bentuk normal pertama (1NF), iaitu untuk memenuhi bentuk normal kedua (2NF), bentuk normal pertama (1NF) mesti terlebih dahulu dipenuhi. Selepas memenuhi 1NF, semua lajur dalam jadual mesti bergantung pada kunci utama, dan tidak boleh ada mana-mana lajur yang tidak mempunyai hubungan dengan kunci utama, yang bermaksud bahawa jadual hanya menerangkan satu perkara

Contohnya: jadual pesanan hanya menerangkan maklumat berkaitan pesanan, jadi semua medan mesti berkaitan dengan id pesanan; tidak boleh muncul dalam satu jadual pada masa yang sama dan maklumat produk seperti yang ditunjukkan di bawah:

3NF Mesti terlebih dahulu memenuhi bentuk normal kedua (2NF), yang memerlukan: Setiap lajur dalam jadual hanya berkaitan secara langsung dengan kunci utama dan tidak berkaitan secara tidak langsung Setiap lajur dalam jadual hanya boleh bergantung pada kunci utama.

Contohnya: jadual pesanan perlu mempunyai maklumat berkaitan pelanggan Selepas jadual pelanggan dipisahkan, jadual pesanan hanya perlu mempunyai satu ID pengguna dan tiada maklumat pelanggan lain. Kerana maklumat pelanggan lain berkaitan secara langsung dengan ID pengguna, tidak berkaitan secara langsung dengan ID pesanan.

Pelbagai kekangan

Kekangan ialah satu set peraturan yang digunakan untuk mengehadkan ketepatan, kesempurnaan, ketekalan dan kaitan data dalam jadual. Dalam Mysql, kekangan disimpan dalam table_constraints pangkalan data information_schema, dan maklumat kekangan boleh ditanya melalui jadual ini. Seperti yang ditunjukkan di bawah:

BUKAN NULL

Kekangan bukan nol, sama ada nilai lajur ini dibenarkan menjadi NULL, satu perkara yang sangat penting di sini, nilai lalai bagi banyak medan (kecuali masa?) ialah NULL jika tidak dinyatakan, jadi kecuali NULL=NULL, nilai lain tidak sama dengan NULL Seperti "", 0, dsb.

Ubah suai medan kepada 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)

Terdapat masalah lain di sini, untuk nilai lalai ialah NULL tetapi medan tidak ditentukan untuk dimasukkan:

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)

Anda boleh melihat bahawa nilai lajur nama pengguna ialah aksara nol, dan nilai lalainya ialah NULL,
Nilai lalai logip ialah NULL, tetapi nilai NULL dibenarkan untuk dimasukkan, jadi nilai NULL dipaparkan di sini.

Semaknya~ Kerana NULL adalah nilai lalai, tetapi nilai NULL tidak dibenarkan, jadi itu bermakna medan nama pengguna kini tidak mempunyai nilai Kerana SQL_MODE, ia hanya akan memberi amaran dan tidak melaporkan ralat secara langsung kami menentukan SQL_MODE sebagai 'STRICT_ALL_TABLES', ralat berikut akan dilaporkan apabila memasukkan:

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

UNIK

unik mewakili kekangan unik: kekangan unik bermakna lajur atau gabungan lajur jadual yang ditentukan tidak boleh diulang untuk memastikan keunikan data Walaupun kekangan unik tidak membenarkan nilai pendua, boleh menjadi berbilang null, dan. jadual yang sama boleh mempunyai berbilang kekangan unik, kekangan yang menggabungkan berbilang lajur. Apabila mencipta kekangan unik, jika anda tidak memberikan nama kekangan yang unik, ia akan lalai kepada nama lajur, dan MySQL akan mencipta indeks unik secara lalai pada lajur kekangan unik.

Tambah kekangan unik:

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'

KUNCI UTAMA

Kekangan kunci utama adalah bersamaan dengan gabungan kekangan unik + kekangan bukan nol Lajur kekangan kunci utama tidak membenarkan nilai pendua atau nol. Jika ia merupakan kekangan kunci utama yang menggabungkan berbilang lajur, tiada satu pun daripada lajur ini dibenarkan mempunyai nilai nol dan nilai gabungan tidak dibenarkan untuk diulang. Setiap jadual hanya membenarkan satu kunci utama paling banyak Kekangan kunci utama boleh dibuat pada peringkat lajur atau peringkat jadual Nama kunci utama MySQL sentiasa UTAMA Apabila membuat kekangan kunci utama, sistem akan lalai lajur dan jadual di mana ia berada. Cipta indeks unik yang sepadan pada gabungan lajur.

Operasi adalah seperti berikut:

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

KUNCI ASING

Kekangan kunci asing memastikan integriti rujukan antara satu atau dua jadual Kekunci asing dibina di atas hubungan rujukan antara dua medan satu jadual atau dua medan dua jadual . Maksudnya, nilai kunci asing jadual hamba mesti ditemui dalam jadual induk atau kosong Apabila rekod jadual induk dirujuk oleh jadual hamba, rekod jadual induk tidak akan dipadamkan anda ingin memadam data, anda perlu memadamkan jadual hamba terlebih dahulu Data dalam jadual bergantung pada rekod, dan kemudian data jadual utama boleh dipadamkan. meja. Nota: Lajur rujukan bagi kekangan kunci asing hanya boleh merujuk kepada lajur kunci utama atau kekangan kunci unik dalam jadual utama Dengan mengandaikan bahawa lajur jadual utama yang dirujuk bukan satu-satunya rekod, maka data yang dirujuk daripada jadual akan tidak pasti lokasi rekod Jadual yang sama boleh mempunyai beberapa kekangan kunci asing.
Sekarang, mari buat jadual GROUP untuk merekod maklumat kumpulan pengguna,

 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 |

Kemudian~ tambahkan rekod pada jadual pengguna untuk merekodkan kumpulan pengguna mana yang tergolong

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

//Tambah kunci asing

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

//Sahkan kekangan kunci asing

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

// Ia boleh kosong, tetapi ia tidak boleh menjadi nilai yang tiada dalam jadual rujukan

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

Takrif kunci asing:

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

Operasi melata berikut memerlukan perhatian:

ON DELETE CASCADE: Apabila memadamkan baris dalam jadual induk (rujukan), jika terdapat baris anak dalam jadual anak yang bergantung pada baris induk yang dipadamkan, maka baris anak akan dipadamkan bersama.

ON DELETE SET NULL: Apabila memadamkan baris dalam jadual induk (rujukan), jika terdapat baris anak dalam jadual anak yang bergantung pada baris induk yang dipadamkan, maka ia tidak akan dipadamkan, tetapi lajur kunci asing anak itu baris akan ditetapkan kepada NULL

SEMAK CHECK kekangan adalah untuk melakukan semakan kekangan CHECK apabila memasukkan baris atau mengemas kini baris data ke dalam jadual CHECK menerima ungkapan Jika ungkapan itu BENAR, sisipan dibenarkan Jika ungkapan itu SALAH. Dalam Versi MariaDB10.2 hanya mula menyokong CHECK.

Kekangan SEMAK biasa termasuk:

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>Contoh: Semak sama ada panjang nama pengguna lebih besar daripada 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` */

Perkara ini kelihatan sangat tidak berguna Nampaknya penilaian data biasanya dilakukan pada lapisan perniagaan, dan pangkalan data hanya perlu menyimpan data.

Atas ialah kandungan terperinci Ringkasan pembelajaran Mariadb (5): kekangan jadual pangkalan data dan tiga paradigma. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!