SQL CHECK CONSTRAINT

測試環境為 CentOS 8 x86_64

CHECK CONSTRAINT 可以檢查輸入資料是否符合預期,避免儲存成錯誤的資料,我們可以在建立欄位 ( CREATE TABLE ) 時就先設定好,或是之後可以透過 ALTER TABLE 來修改或新增.

Constraint 的類型如下.

1. PRIMARY KEY : – https://benjr.tw/104182
   Primary Key (主鍵) 必須為 not null (不能為空) 並且要 Unique (唯一).資料表只有一個欄位可以為 Primary Key.
2. FOREIGN KEY :
   Sets the column to reference the primary key on another table.
3. UNIQUE : – https://benjr.tw/104182
   Unique Key (唯一鍵) 不能重覆,但是可以為 null (空).
4. CHECK : – 這邊介紹
   檢查輸入資料是否滿足設定條件.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed

可以透過以下兩種方式來定義 CHECK CONSTRAINT :

1. CHECK(expression)
2. CONSTRAINT [constraint_name] CHECK (expression)

定義包含 t1 (INT) 與 t2(INT), 並透過 CHECK 檢查欄位值是否符合設定.

MariaDB [testdb]> CREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b));
Query OK, 0 rows affected (0.008 sec)

透過下面方式來檢視欄位的 CHECK CONSTRAINT.

MariaDB [testdb]> SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+------------+-----------------+
| a               | t1         | CHECK           |
| b               | t1         | CHECK           |
| a_greater       | t1         | CHECK           |
+-----------------+------------+-----------------+
3 rows in set (0.002 sec)

輸入資料時會檢查 a>2 , b>2 而且需要 a>b 才能輸入.

MariaDB [testdb]> INSERT INTO t1(a , b) VALUES(4,3);
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO t1(a , b) VALUES(4,2);
ERROR 4025 (23000): CONSTRAINT `t1.b` failed for `testdb`.`t1`
MariaDB [testdb]> INSERT INTO t1(a , b) VALUES(4,5);
ERROR 4025 (23000): CONSTRAINT `a_greater` failed for `testdb`.`t1`

MariaDB [testdb]> SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|    4 |    3 |
+------+------+
1 row in set (0.001 sec)

ALTER MODIFY

後續要改變 CHECK 資料,可以使用 ALTER CHANGE / MODIFY 語法來移除 a 跟 b 欄位的 CHECK.

MariaDB [testdb]> ALTER TABLE t1 MODIFY a INT(11);
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> ALTER TABLE t1 MODIFY b INT(11);
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+------------+-----------------+
| a_greater       | t1         | CHECK           |
+-----------------+------------+-----------------+
1 row in set (0.001 sec)

MariaDB [testdb]> ALTER TABLE t1 MODIFY a INT CHECK (a>2);
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> ALTER TABLE t1 MODIFY b INT CHECK (b>2);
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+------------+-----------------+
| a               | t1         | CHECK           |
| b               | t1         | CHECK           |
| a_greater       | t1         | CHECK           |
+-----------------+------------+-----------------+
3 rows in set (0.001 sec)

ALTER DROP / ADD

要改變 CHECK Constraints 資料,可以使用 ALTER DROP 語法來移除.

MariaDB [testdb]> ALTER TABLE t1 DROP CONSTRAINT a_greater;
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+------------+-----------------+
| a               | t1         | CHECK           |
| b               | t1         | CHECK           |
+-----------------+------------+-----------------+
2 rows in set (0.001 sec)

用 ALTER ADD 語法增加 CONSTRAINT 定義.

MariaDB [testdb]> ALTER TABLE testdb.t1 ADD CONSTRAINT a_greater CHECK (a>b);
Query OK, 1 row affected (0.015 sec)               
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+------------+-----------------+
| a               | t1         | CHECK           |
| b               | t1         | CHECK           |
| a_greater       | t1         | CHECK           |
+-----------------+------------+-----------------+
3 rows in set (0.002 sec)