SQL 約束（Constraints）

SQL 約束用於規定表中的資料規則。

如果存在違反約束的資料行為，行為會被約束終止。

約束可以在建立表時規定（通過 CREATE TABLE 語句），或者在表建立之後規定（通過 ALTER TABLE 語句）。

SQL CREATE TABLE + CONSTRAINT 語法

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

在 SQL 中，我們有如下約束：

• NOT NULL - 指示某列不能儲存 NULL 值。
• UNIQUE - 保證某列的每行必須有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助於更容易更快速地找到表中的一個特定的記錄。
• FOREIGN KEY - 保證一個表中的資料匹配另一個表中的值的參照完整性。
• CHECK - 保證列中的值符合指定的條件。
• DEFAULT - 規定沒有給列賦值時的預設值。

在下面的章節，我們會詳細講解每一種約束。

---

SQL NOT NULL 約束

在預設的情況下，表的列接受 NULL 值。

NOT NULL 約束強制列不接受 NULL 值。

NOT NULL 約束強制欄位始終包含值。這意味著，如果不向欄位新增值，就無法插入新記錄或者更新記錄。

下面的 SQL 強制 "P_Id" 列和 "LastName" 列不接受 NULL 值：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

---

SQL UNIQUE 約束

SQL UNIQUE 約束

UNIQUE 約束唯一標識資料庫表中的每條記錄。

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束。

請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。

CREATE TABLE 時的 SQL UNIQUE 約束

下面的 SQL 在 "Persons" 表建立時在 "P_Id" 列上建立 UNIQUE 約束：

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

如需命名 UNIQUE 約束，並定義多個列的 UNIQUE 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

 

---

ALTER TABLE 時的 SQL UNIQUE 約束

當表已被建立時，如需在 "P_Id" 列建立 UNIQUE 約束，請使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD UNIQUE (P_Id)

如需命名 UNIQUE 約束，並定義多個列的 UNIQUE 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

---

撤銷 UNIQUE 約束

如需撤銷 UNIQUE 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

---

SQL PRIMARY KEY 約束

SQL PRIMARY KEY 約束

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄。

主鍵必須包含唯一的值。

主鍵列不能包含 NULL 值。

每個表都應該有一個主鍵，並且每個表只能有一個主鍵。

---

CREATE TABLE 時的 SQL PRIMARY KEY 約束

下面的 SQL 在 "Persons" 表建立時在 "P_Id" 列上建立 PRIMARY KEY 約束：

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

如需命名 PRIMARY KEY 約束，並定義多個列的 PRIMARY KEY 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

註釋：在上面的例項中，只有一個主鍵 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由兩個列（P_Id 和 LastName）組成的。

---

ALTER TABLE 時的 SQL PRIMARY KEY 約束

當表已被建立時，如需在 "P_Id" 列建立 PRIMARY KEY 約束，請使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

如需命名 PRIMARY KEY 約束，並定義多個列的 PRIMARY KEY 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

註釋：如果您使用 ALTER TABLE 語句新增主鍵，必須把主鍵列宣告為不包含 NULL 值（在表首次建立時）。

---

撤銷 PRIMARY KEY 約束

如需撤銷 PRIMARY KEY 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

---

SQL FOREIGN KEY 約束

SQL FOREIGN KEY 約束

一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。

讓我們通過一個例項來解釋外來鍵。請看下面兩個表：

"Persons" 表：

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

"Orders" 表：

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

請注意，"Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列。

"Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 約束用於預防破壞表之間連線的行為。

FOREIGN KEY 約束也能防止非法資料插入外來鍵列，因為它必須是它指向的那個表中的值之一。

---

CREATE TABLE 時的 SQL FOREIGN KEY 約束

下面的 SQL 在 "Orders" 表建立時在 "P_Id" 列上建立 FOREIGN KEY 約束：

MySQL：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

如需命名 FOREIGN KEY 約束，並定義多個列的 FOREIGN KEY 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

 

---

ALTER TABLE 時的 SQL FOREIGN KEY 約束

當 "Orders" 表已被建立時，如需在 "P_Id" 列建立 FOREIGN KEY 約束，請使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 約束，並定義多個列的 FOREIGN KEY 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

---

撤銷 FOREIGN KEY 約束

如需撤銷 FOREIGN KEY 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access：

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

---

SQL CHECK 約束

SQL CHECK 約束

CHECK 約束用於限制列中的值的範圍。

如果對單個列定義 CHECK 約束，那麼該列只允許特定的值。

如果對一個表定義 CHECK 約束，那麼此約束會基於行中其他列的值在特定的列中對值進行限制。

---

CREATE TABLE 時的 SQL CHECK 約束

下面的 SQL 在 "Persons" 表建立時在 "P_Id" 列上建立 CHECK 約束。CHECK 約束規定 "P_Id" 列必須只包含大於 0 的整數。

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

如需命名 CHECK 約束，並定義多個列的 CHECK 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

 

---

ALTER TABLE 時的 SQL CHECK 約束

當表已被建立時，如需在 "P_Id" 列建立 CHECK 約束，請使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

如需命名 CHECK 約束，並定義多個列的 CHECK 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

 

---

撤銷 CHECK 約束

如需撤銷 CHECK 約束，請使用下面的 SQL：

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL：

ALTER TABLE Persons
DROP CHECK chk_Person

---

SQL DEFAULT 約束

SQL DEFAULT 約束

DEFAULT 約束用於向列中插入預設值。

如果沒有規定其他的值，那麼會將預設值新增到所有的新記錄。

---

CREATE TABLE 時的 SQL DEFAULT 約束

下面的 SQL 在 "Persons" 表建立時在 "City" 列上建立 DEFAULT 約束：

My SQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

通過使用類似 GETDATE() 這樣的函式，DEFAULT 約束也可以用於插入系統值：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

 

---

ALTER TABLE 時的 SQL DEFAULT 約束

當表已被建立時，如需在 "City" 列建立 DEFAULT 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Oracle：

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

---

撤銷 DEFAULT 約束

如需撤銷 DEFAULT 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT