SQL約束

JavaCoderPan發表於2023-03-11

1、約束

1.1 約束
在 SQL 中，約束是規定表中的資料規則。若存在違反約束的行為，行為就會被阻止。它能幫助管理員更好地管理資料庫，並且確保資料庫中資料的正確性和有效性。例如在後臺的資料庫設計中對要輸入的資料進行核查或判斷，再決定是否寫入資料庫，這都是約束的應用。

1.1、非空約束NOT NULL

NOT NULL 約束強制列不接受 NULL 值，強制欄位始終包含值，這意味著，如果不向欄位新增值，就無法插入新紀錄或者更新記錄。
下面的 SQL 強制 ID 列、 LastName 列以及 FirstName 列不接受 NULL 值：

CREATE TABLE `Persons` (
    `ID` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255) NOT NULL,
    `Age` int
);

在一個已建立的表的 Age 欄位中新增 NOT NULL 約束如下所示：

ALTER TABLE `Persons`
	MODIFY `Age` int NOT NULL;

在一個已建立的表的 Age 欄位中刪除 NOT NULL 約束如下所示：

ALTER TABLE `Persons`
	MODIFY `Age` int NULL;

注意
不要把 NULL 值與空串相混淆。NULL 值是沒有值，

它不是空串。如果指定' '（兩個單引號，其間沒有字元），這
在 NOT NULL 列中是允許的。空串是一個有效的值，它不是無
值。NULL 值用關鍵字 NULL 而不是空串指定。

1.2唯一約束UNIQUE

在前面的學習中我們知道了 NOT NULL 約束是強制列不接受 NULL 值。
在有些情況下，我們不希望一個表中出現重複的記錄，這時候我們需要用到 UNIQUE 約束來解決這些問題。

UNIQUE約束唯一標識資料庫表中的每條記錄
UNIQUE 和主鍵約束均為列或列集合提供了唯一性的保證
主鍵約束會自動定義一個UNIQUE約束，或者說主鍵約束是一種特殊的UNIQUE約束。但是二者有明顯的區別：每個表可以有多個UNIQUE約束，但是隻能有一個主鍵約束。

1.2.1CREATE TABLE 時的 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 約束:
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`)
)

1.2.2 ALTER TABLE 時的 UNIQUE 約束

當表已被建立時，在P_id列建立UNIQUE約束：
MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
	ADD UNIQUE (`P_Id`)

當表已被建立時，需命名UNIQUE約束，並定義多個列的UNIQUE約束：
MySQL / SQL Server / Oracle / MS Access

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

1.2.3撤銷UNIQUE約束

如需撤銷 UNIQUE 約束 :

MySQL

ALTER TABLE `Persons`
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
DROP CONSTRAINT uc_PersonID

1.3主鍵約束PRIMARY KEY

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄，簡單的說，PRIMARY KEY = UNIQUE + NOT NULL ,從技術的角度來看，PRIMARY KEY 和 UNIQUE 有很多相似之處。但還是有以下區別：

NOT NULL UNIQUE 可以將表的一列或多列定義為唯一性屬性，而 PRIMARY KEY 設為多列時，僅能保證多列之和是唯一的，具體到某一列可能會重複。
PRIMARY KEY 可以與外來鍵配合，從而形成主從表的關係，而 NOT NULL UNIQUE 則做不到這一點

如：
表一：使用者id（主鍵），使用者名稱
表二：銀行卡號id（主鍵），使用者id（外來鍵）
則表一為主表，表二為從表

更大的區別在邏輯設計上。
PRIMARY KEY 一般在邏輯設計中用作記錄標識，這也是設定 PRIMARY KEY 的本來用意，而 UNIQUE 只是為了保證域/域組的唯一性。

1.3.1CREATE TABLE時新增PRIMARY KEY 約束

?我們可以透過下面的例項來感受一下如何新增 PRIMARY KEY 約束。

前面我們建立了個人資訊資料表 Persons，我們希望每個人的識別符號 P_Id 都是唯一的。
下面的 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`)
)

執行輸出結果:

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`)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

1.3.2ALTER TABLE 時新增主鍵約束

如果我們在建立 Persons 時沒有指定建立主鍵約束，此時是否需要刪除表再重新寫帶有主鍵約束的建表語句呢？這裡我們可以使用 ALTER TABLE 語句在建立表後新增主鍵約束。

當表已被建立時，如需在 P_Id 列建立 PRIMARY KEY 約束：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE `Persons`
	ADD PRIMARY KEY (`P_Id`)

執行輸出結果

mysql> CREATE TABLE Persons
    -> (
    ->     `P_Id` int NOT NULL,
    ->     `LastName` varchar(255) NOT NULL,
    ->     `FirstName` varchar(255),
    ->     `Address` varchar(255),
    ->     `City` varchar(255)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE Persons
    -> ADD PRIMARY KEY (P_Id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

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

MySQL / SQL Server / Oracle / MS Access

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

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

刪除某表的主鍵約束：

ALTER TABLE `table_name` DROP PRIMARY KEY;

1.4外來鍵約束

1.4.1什麼是外來鍵

首先在學習外來鍵約束之前我們先來認識一下什麼是外來鍵。一個表中的 FOREIGN KEY 指向另一個表中的 UNIQUE KEY 。

讓我們看了例子，如果一個欄位 X 在一張表（表 1 ）中是關鍵字，而在另一張表（表 2 ）中不是關鍵字，則稱欄位 X 為表 2 的外來鍵。

1.4.2外來鍵的作用

外來鍵最根本的作用：保證資料的完整性和一致性。

現在有兩張表——學生表和院系表，這裡的院系就是學生表的外來鍵，外來鍵表是學生表，主鍵表是院系表。假如院系表中的某個院系被刪除了，那麼在學生表中要想查詢這個被刪除的院系號所對應的院資訊就會報錯，因為已經不存在這個繫了，所以，刪除院系表（主鍵表）時必須刪除其他與之關聯的表，這裡就說明了外來鍵的作用，保持資料的一致性、完整性。當然反過來講，你刪除學生表中的記錄，並不影響院系表中的資料，你查詢院系號也能正確查詢。所以刪除外來鍵表中的資料並不影響主鍵表。

1.4.3外來鍵約束

外來鍵約束是指用於在兩個表之間建立關係，需要指定引用主表的哪一列。接下來在實際操做中讓我們感受一下外來鍵約束的用法。

1.4.3.1CREATE TABLE 時的 SQL FOREIGN KEY 約束

?我們可以透過下面的例項來感受一下如何新增 FOREIGN KEY 約束。

在 "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)
)

其中
NOT NULL 表示該欄位不為空
REFERENCES 表示引用一個表

如需命名 FOREIGN KEY 約束，並定義多個列的 FOREIGN KEY 約束:

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

其中
CONSTRAINT 表示約束，後面接約束名稱，常用於建立約束和刪除約束；

1.4.3.2ALTER TABLE 時的 SQL FOREIGN KEY 約束

當 "Orders" 表已被建立時，如需在 "P_Id" 列建立 FOREIGN KEY 約束:

MySQL / SQL Server / Oracle / MS Access

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

如需命名 FOREIGN KEY 約束，並定義多個列的 FOREIGN KEY 約束:

MySQL / SQL Server / Oracle / MS Access

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

1.4.3.3撤銷FOREIGN KEY 約束

?我們可以透過下面的例項來感受一下如何刪除 FOREIGN KEY 約束。

如需撤銷 FOREIGN KEY 約束:
MySQL

ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access

ALTER TABLE `Orders`
DROP CONSTRAINT fk_PerOrders

1.5檢查CHECK

CHECK約束用於限制列中的值的範圍，評估插入或修改後的值。滿足條件的值將會插入表中，否則將放棄插入操作。
可以為同一列指定多個CHECK約束

CHECK約束既可以用於某一列也可以用於某張表：

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

定義 CHECK 約束條件在某種程度上類似於編寫查詢的 WHERE 子句，使用不同的比較運算子（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）編寫其布林表示式，該布林表示式將返回 TRUE、FALSE 或 UNKNOWN 。

當條件中存在 NULL 值時，CHECK約束將返回 UNKNOWN 值。

CHECK 約束主要用於透過將插入的值限制為遵循定義的值、範圍或格式規則的值來強制域完整性。

CREATE DATABASE IF NOT EXISTS hardy_db 
	default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

DROP TABLE IF EXISTS lesson;

建立表結構時可以使用 CHECK 約束，也可以給已建立的表增加 CHECK 約束。

?我們舉一個例子感受一下它的用法：

假如我們想建立一個簡單的課程表 courses ，表中每一條資料記錄著課程編號 id、課程名稱 name 、學生總數 student_count 、建立課程時間 created_at 以及授課教師編號 teacher_id。其中課程編號 id 為主鍵。

根據基本常識，學生總數 student_count 一定是非負值，在這裡我們設定它必須為正整數，可以使用 CHECK 約束。

在不同的 SQL 軟體中，語法會有些不同，在本文中會介紹 CHECK 約束在各個 SQL 軟體中的使用。

1.5.1建立表（CREATE TABLE）時新增 CHECK約束

在建立課程表 courses 時，給學生總數 student_count 欄位加上一個大於 0 的約束。
[CHECK (student_count > 0)]
MYSQL：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int
CHECK (`student_count` > 0),
`name` varchar(255),`student_count` int,
`created_at` date,
`teacher_id` int
)

為多個列新增 CHECK 約束

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

如果想為一個表中多個欄位新增約束，直接在 CHECK 關鍵字後的括號內新增，兩個約束間使用 AND 關鍵字連線。

為 CHECK 約束命名
MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

核心部分的基本語法為：

[CONSTRAINT <constraint name>] CHECK (<condition>)

其中：

約束關鍵字 CONSTRAINT：表示其後面接的內容為約束

constraint name：為約束名稱

關鍵字 CHECK：表示檢查約束

condition：被約束內容

1.5.2表已存在時新增CHECK約束

課程表 courses 已存在的情況下為學生總數 student_count 欄位新增一個大於 0 的 CHECK 約束：
MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE `courses`
	ADD CHECL (`student_count` > 0)

或

ALTER TABLE `courses`
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);

1.5.3撤銷CHECK約束

如果想要撤銷 CHECK 約束，可以使用 DROP 關鍵字。
MYSQL：

ALTER TABLE `courses` 
DROP CHECK chk_courses

SQL Server / Oracle / MS Access：

ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

1.6預設約束 DEFAULT

預設值（Default）”的完整稱呼是“預設值約束（Default Constraint）”。MySQL 預設值約束用來指定某列的預設值。

1.6.1 DEFAULT 約束用法

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

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

例如女同學較多，性別就可以預設為“女”，如果插入一條新的記錄時沒有為這個欄位賦值，那麼系統會自動為這個欄位賦值為“女”。

1.6.2 CREATE TABLE 時的 DEFAULT 約束

使用 DEFAULT 關鍵字設定預設值約束，具體的語法規則如下所示：

<欄位名> <資料型別> DEFAULT <預設值>

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

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) DEFAULT 'Sandnes'
)

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

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

1.6.3 ALTER TABLE 時的 DEFAULT 約束

?如果表已被建立時，想要在 City 列建立 DEFAULT 約束，請使用下面的 SQL：

MYSQL

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE `Persons`
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`

1.6.4 撤銷 DEFAULT 約束

?如需撤銷 Persons表的 DEFAULT 約束 :

MYSQL:

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE `Persons`
ALTER COLUMN `City` DROP DEFAULT

【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束
2018-07-26
SQLAINull
sql: 查詢約束
2016-10-13
SQL
SQL之延遲約束
2013-12-24
SQL
SQL FOREIGN KEY 約束
2017-12-19
SQL
Sql Server系列：鍵和約束
2014-11-24
SQLServer
SQL Server唯一約束的使用
2009-02-06
SQLServer
SQL教程——常見的約束型別
2020-12-01
SQL型別
Javaweb-約束-外來鍵約束
2024-09-07
JavaWeb
SQL Server 資料表程式碼建立約束
2018-06-13
SQLServer
SQL的主鍵和外來鍵約束
2015-04-22
SQL
Oracle定義約束外來鍵約束
2009-01-24
Oracle
Oracle約束
2017-06-23
Oracle
oracle 約束
2013-11-28
Oracle
MySQL 約束
2024-10-28
MySql
03約束
2024-10-19
綜合約束
2024-03-23
SQL外來鍵約束的含義及建立
2021-09-09
SQL
SQL Server中使用Check約束達到提升效能
2021-09-09
SQLServer
【學習】SQL基礎-014-約束
2018-04-10
SQL
（10）邏輯綜合新增約束（環境約束）
2024-07-08
SQL Server2008建立約束圖解
2015-04-22
SQLServer圖解
SQL Server約束和DML觸發器的比較
2009-01-12
SQLServer觸發器
Oracle SQL細節總結之constraint約束
2012-09-16
OracleSQLAI
SQL SERVER中找出拙劣的約束，索引，外來鍵
2009-12-29
SQLServer索引
約束介紹
2024-03-28
差分約束
2024-05-21
sql server中預設約束的通用sql指令碼的刪除方法
2009-02-11
SQLServer指令碼
【MySQL】MySQL基礎（SQL語句、約束、資料型別）
2021-09-19
MySql資料型別
C# 泛型引用型別約束值型別約束
2015-05-07
C#泛型型別
SQLServer約束介紹
2018-09-10
SQLServer
MySQL自增約束
2020-11-23
MySql
MySQL 欄位約束
2022-06-06
MySql
約束CONSTRAINT
2020-10-12
AI
《學習》6約束
2018-03-23
constraint約束
2014-04-24
AI
oracle中的約束
2014-09-03
Oracle
oracle 約束詳解
2014-09-17
Oracle
Oracle約束簡介
2016-03-06
Oracle

SQL約束

1、約束

1.1、非空約束NOT NULL

1.2唯一約束UNIQUE

1.2.1CREATE TABLE 時的 UNIQUE 約束

1.2.2 ALTER TABLE 時的 UNIQUE 約束

1.2.3撤銷UNIQUE約束

1.3主鍵約束PRIMARY KEY

1.3.1CREATE TABLE時新增PRIMARY KEY 約束

1.3.2ALTER TABLE 時新增主鍵約束

1.4外來鍵約束

1.4.1什麼是外來鍵

1.4.2外來鍵的作用

1.4.3外來鍵約束

1.4.3.1CREATE TABLE 時的 SQL FOREIGN KEY 約束

1.4.3.2ALTER TABLE 時的 SQL FOREIGN KEY 約束

1.4.3.3撤銷FOREIGN KEY 約束

1.5檢查CHECK

1.5.1建立表（CREATE TABLE）時新增 CHECK約束

1.5.2表已存在時新增CHECK約束

1.5.3撤銷CHECK約束

1.6預設約束 DEFAULT

1.6.1 DEFAULT 約束用法

1.6.2 CREATE TABLE 時的 DEFAULT 約束

1.6.3 ALTER TABLE 時的 DEFAULT 約束

1.6.4 撤銷 DEFAULT 約束

相關文章