SQL CHECK CONSTRAINT

測試環境為 CentOS 8 x86_64

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

Constraint 的類型如下.

  1. PRIMARY KEY :
    Sets the column for referencing rows. Values must be unique and not null.
  2. FOREIGN KEY :
    Sets the column to reference the primary key on another table.
  3. UNIQUE :
    Requires values in column or columns only occur once in the table.
  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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料