SQL - 檢查約束

---

---

SQL CHECK 約束

SQL 的CHECK 約束用於向表的列新增條件。

在列上新增檢查約束後，它可確保輸入到該列的資料滿足指定的條件。如果特定記錄不滿足這些條件，則資料庫將阻止您插入或更新該記錄。

假設我們有一個名為 CUSTOMERS 的表，其中包含一個 AGE 列。我們可以在此列上新增CHECK 約束，以確保輸入的年齡始終為正數且不超過 50 歲。如果有人試圖輸入負年齡或超過 50 歲的年齡，資料庫將拒絕它，確保您的資料保持準確和有效。

單列檢查約束

要在列級別新增檢查約束，我們必須在建立表時在列名之後指定檢查約束。

語法

以下是指定單列檢查約束的語法：

CREATE TABLE table_name (
   column_name data_type CHECK (condition)
);

示例

在下面的查詢中，我們正在建立一個名為 CUSTOMERS 的表。在這裡，我們正在 AGE 列上指定列級檢查約束，該約束僅允許插入客戶年齡值大於“20”的記錄：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE>=20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

驗證

要驗證是否已向 AGE 列新增檢查約束，我們可以在 MySQL 資料庫中使用以下查詢：

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

輸出

上述查詢將顯示 CUSTOMERS 表的所有詳細資訊，包括有多少列具有檢查約束以及我們在表中指定了哪些約束，如下所示：

現在，要驗證 CHECK 約束是否正常工作，讓我們向 CUSTOMERS 中插入一條記錄，其中 AGE 包含小於 20 的值（不滿足給定條件）：

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 15, 'Ahmedabad', 2000.00 );

上述查詢的輸出如下所示：

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

多列檢查約束

我們還可以透過指定必須滿足這些列的值組合的條件，在表的多個列上新增檢查約束。

假設我們有一個表，其中包含產品的詳細資訊，包括它們的開始日期和結束日期。我們可以新增一個 CHECK 約束，以確保結束日期始終大於或等於開始日期。在這種情況下，約束正在檢查同一行中兩列（開始日期和結束日期）的值，以確保它們遵循特定的關係。

示例

在下面的示例中，我們正在 CUSTOMERS 表的多個列（AGE 和 SALARY）上指定列級檢查約束。在這裡，AGE 列僅允許 AGE 大於或等於 20 的記錄，SALARY 列僅允許 SALARY 大於 20000 的記錄：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE >= 20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2) CHECK(SALARY >= 20000),
   PRIMARY KEY (ID)
);

驗證

要驗證檢查約束是否應用於這兩列，我們可以在 MySQL 資料庫中使用以下查詢：

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

輸出

它將顯示已建立表的全部詳細資訊，包括有多少列具有檢查約束以及我們在表中指定的約束：

現在，我們正在向 CUSTOMERS 表中插入值，其中年齡小於 20，工資小於 20000。

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 15, 'Ahmedabad', 2000.00 );

上述查詢丟擲錯誤，因為 AGE 和 SALARY 列中傳遞的值不滿足 CHECK 約束：

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

表級檢查約束

為了確保表級檢查約束，我們必須在完成表建立之前使用檢查約束。

語法

以下是指定表級檢查約束的語法：

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,...,
   CONSTRAINT constraint_name CHECK(column_name condition_value)
);

示例

在以下 SQL 查詢中，我們正在建立一個名為 PRODUCTS 的表。在這裡，我們正在 DATE_OF_ORDER 列上指定表級檢查約束，該約束僅允許插入 DATE_OF_ORDER 小於（早於）“2023-02-09”的記錄：

CREATE TABLE PRODUCTS(
   PID INT NOT NULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER Date NOT NULL,
   PRICE INT,
   PRIMARY KEY(PID),
   CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= '2023-02-09')
);

驗證

我們可以使用以下 SQL 查詢驗證已建立表上的 CHECK 約束：

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='PRODUCTS';

輸出

它將顯示已建立表的全部詳細資訊，包括表級別有多少列具有檢查約束，如下所示：

在這裡，我們正在向 PRODUCTS 中插入值，該值在 DATE_OF_ORDER 列上具有小於“2023-02-09”的約束：

INSERT INTO PRODUCTS VALUES
(001, 'Nike Shoe', 'Ranchi', '2023-01-11', 2000);

以下是上述查詢的輸出：

Query OK, 1 row affected (0.01 sec)

現有列的檢查約束

我們可以使用ALTER TABLE語句向表的現有列新增檢查約束。

語法

以下是向現有表新增檢查約束的語法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(ColumnName condition_value);

示例

在以下查詢中，我們正在建立一個名為 CUSTOMERS 的表：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

要向 AGE 列新增檢查約束，我們使用以下查詢：

ALTER TABLE CUSTOMERS 
ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);

驗證

要驗證表建立後是否應用了檢查約束，請使用以下 SQL 查詢：

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

輸出

它將顯示錶的全部資訊，包括我們新增到年齡列的約束：

刪除檢查約束

如果有一種方法可以在列上新增約束，那麼您也必須能夠從該列中刪除約束。為此，可以使用ALTER DROP語句。

語法

以下是從表中刪除檢查約束的語法：

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

示例

下面的示例顯示如何從上面建立的 CUSTOMERS 表中刪除檢查約束：

ALTER TABLE CUSTOMERS 
DROP CONSTRAINT Constraint_Age;

驗證

使用以下 SQL 查詢，我們正在驗證約束是否已刪除：

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

輸出

我們可以看到，新增到年齡列的檢查約束已刪除：