SQL約束是數據庫設計的核心機制，用于強制數據完整性和業(yè)務規(guī)則。以下按約束類型分類解析，包含基礎語法、參數說明、應用場景和實戰(zhàn)示例，僅供參考。
一、SQL約束的基礎概念
作用：在數據插入/更新時自動校驗，防止非法數據破壞完整性
分類：
- 列級約束：直接定義在字段后（
CREATE TABLE時） - 表級約束：獨立聲明在所有字段后（支持多字段組合約束）
共性特征： - 違反約束時操作被終止，拋出錯誤代碼（如MySQL 1062主鍵沖突）
- 支持
CREATE TABLE或ALTER TABLE兩種定義方式
數據完整性類型：
二、SQL約束類型詳解
1. NOT NULL 約束
作用：禁止字段存儲NULL值（空值 ≠ 0或空字符串）
語法：
-- 創(chuàng)建表時定義
CREATE TABLE 表名 (
字段名 數據類型 NOT NULL
);
-- 修改表結構
ALTER TABLE 表名
MODIFY 字段名 數據類型 NOT NULL; -- MySQL/PostgreSQL
ALTER TABLE 表名
ALTER COLUMN 字段名 SET NOT NULL; -- SQL Server
重要特性：
- SQL Server嚴格區(qū)分
NULL與''
實戰(zhàn)示例：
-- 創(chuàng)建用戶表
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName VARCHAR(50) NOT NULL, -- 用戶名不能為空
BirthDate DATE NOT NULL -- 出生日期必填
);
-- 插入非法數據（觸發(fā)約束）
INSERT INTO Users (UserID, UserName)
VALUES (1, 'Alice');
-- 錯誤: Column 'BirthDate' cannot be null
2. UNIQUE 約束
作用：確保字段值全表唯一（允許多個NULL）
語法：
-- 單字段列級約束
CREATE TABLE 表名 (
字段名 數據類型 UNIQUE
);
-- 多字段表級約束
CREATE TABLE 表名 (
字段1 數據類型,
字段2 數據類型,
CONSTRAINT 約束名 UNIQUE (字段1, 字段2)
);
-- 修改表添加約束
ALTER TABLE 表名
ADD CONSTRAINT 約束名 UNIQUE (字段);
跨數據庫差異：
實戰(zhàn)示例：
-- 創(chuàng)建員工郵箱表
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE, -- 郵箱唯一
Phone VARCHAR(20)
);
-- 添加復合唯一約束（部門+工號）
ALTER TABLE Employees
ADD CONSTRAINT uniq_dept_emp
UNIQUE (DeptID, EmpCode);
-- 插入沖突數據
INSERT INTO Employees (EmpID, Email)
VALUES (1, 'alice@company.com');
INSERT INTO Employees (EmpID, Email)
VALUES (2, 'alice@company.com');
-- 錯誤: Duplicate entry 'alice@company.com'
3. PRIMARY KEY 約束
作用：唯一標識行數據（NOT NULL + UNIQUE）
語法：
-- 單字段主鍵
CREATE TABLE 表名 (
字段名 數據類型 PRIMARY KEY -- 列級
);
-- 多字段主鍵（表級）
CREATE TABLE 表名 (
字段1 數據類型,
字段2 數據類型,
CONSTRAINT pk_name PRIMARY KEY (字段1, 字段2)
);
-- 添加主鍵
ALTER TABLE 表名
ADD PRIMARY KEY (字段); -- 匿名約束
ALTER TABLE 表名
ADD CONSTRAINT pk_name PRIMARY KEY (字段);
核心規(guī)則：
實戰(zhàn)示例：
-- 創(chuàng)建訂單表
CREATE TABLE Orders (
OrderID INT PRIMARY KEY, -- 列級主鍵
OrderDate DATE NOT NULL
);
-- 創(chuàng)建訂單詳情（復合主鍵）
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
CONSTRAINT pk_order_product
PRIMARY KEY (OrderID, ProductID) -- 表級
);
-- 非法數據插入
INSERT INTO Orders (OrderID, OrderDate)
VALUES (1, '2023-01-01');
INSERT INTO Orders (OrderID, OrderDate)
VALUES (1, '2023-02-01');
-- 錯誤: Duplicate entry '1'for key 'PRIMARY'
4. FOREIGN KEY 約束
作用：強制表間引用完整性（子表引用父表主鍵）
語法：
-- 創(chuàng)建表時定義
CREATE TABLE 子表 (
子表字段 數據類型,
CONSTRAINT fk_name
FOREIGN KEY (子表字段)
REFERENCES 父表(父表字段)
[ON DELETE 動作] -- 級聯操作
[ON UPDATE 動作]
);
-- 添加外鍵
ALTER TABLE 子表
ADD CONSTRAINT fk_name
FOREIGN KEY (子表字段)
REFERENCES 父表(父表字段);
級聯操作（可選）：
| |
|---|
NO ACTION | |
CASCADE | |
SET NULL | |
SET DEFAULT | |
實戰(zhàn)示例：
-- 父表：部門
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
-- 子表：員工（帶級聯刪除）
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
DeptID INT,
CONSTRAINT fk_emp_dept
FOREIGN KEY (DeptID)
REFERENCES Departments(DeptID)
ON DELETE CASCADE -- 部門刪除時員工自動刪除
);
-- 插入關聯數據
INSERT INTO Departments VALUES (1, 'IT');
INSERT INTO Employees VALUES (101, 1);
-- 測試級聯刪除
DELETE FROM Departments WHERE DeptID = 1;
-- 結果：Employees中EmpID=101的記錄自動刪除
5. CHECK 約束
作用：定義字段值的業(yè)務規(guī)則（類似WHERE條件）
語法：
-- 列級約束
CREATE TABLE 表名 (
字段名 數據類型 CHECK (條件)
);
-- 表級多字段約束
CREATE TABLE 表名 (
字段1 數據類型,
字段2 數據類型,
CONSTRAINT chk_name CHECK (字段1 > 字段2)
);
-- 添加約束
ALTER TABLE 表名
ADD CONSTRAINT chk_name CHECK (條件);
特殊規(guī)則：
實戰(zhàn)示例：
-- 創(chuàng)建賬戶表
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance DECIMAL(10,2) CHECK (Balance >= 0), -- 余額不可負
Status VARCHAR(10) CHECK (Status IN ('ACTIVE', 'CLOSED'))
);
-- 添加自定義規(guī)則（開戶日期早于今日）
ALTER TABLE Accounts
ADD CONSTRAINT chk_open_date
CHECK (OpenDate < GETDATE());
-- 違反約束測試
INSERT INTO Accounts
VALUES (1, -100.00, 'ACTIVE');
-- 錯誤: Check constraint 'Balance>=0' violated
6. DEFAULT 約束
作用：字段未賦值時自動填充默認值
語法：
-- 創(chuàng)建表時定義
CREATE TABLE 表名 (
字段名 數據類型 DEFAULT 默認值
);
-- 修改默認值
ALTER TABLE 表名
ALTER COLUMN 字段名 SET DEFAULT 值; -- MySQL/PostgreSQL
ALTER TABLE 表名
ADD CONSTRAINT 約束名 DEFAULT 值 FOR 字段; -- SQL Server
常用默認值：
- 系統(tǒng)函數：
DEFAULT GETDATE()（當前時間） - 表達式：
DEFAULT (UUID())（生成唯一ID）
實戰(zhàn)示例：
-- 創(chuàng)建用戶注冊表
CREATE TABLE Registrations (
UserID INT PRIMARY KEY,
RegDate DATE DEFAULT GETDATE(), -- 自動填充注冊日期
Status VARCHAR(10) DEFAULT 'PENDING'
);
-- 插入數據（忽略默認字段）
INSERT INTO Registrations (UserID) VALUES (1001);
-- 查詢結果
SELECT * FROM Registrations;
/*
UserID | RegDate | Status
1001 | 2023-10-05 | PENDING
*/
7. INDEX（索引）
嚴格說索引非約束，但用于加速唯一性校驗
作用：快速定位數據（UNIQUE/PRIMARY KEY自動創(chuàng)建）
語法：
CREATE INDEX 索引名 ON 表名 (字段);
CREATE UNIQUE INDEX 索引名 ON 表名 (字段); -- 唯一索引
與約束關系：
三、SQL約束管理技巧
1. 查看約束
-- SQL Server
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
-- MySQL
SHOW CREATE TABLE 表名;
2. 刪除約束
ALTER TABLE 表名 DROP CONSTRAINT 約束名; -- 通用
ALTER TABLE 表名 DROP PRIMARY KEY; -- 主鍵
ALTER TABLE 表名 DROP INDEX 索引名; -- 索引
3. 臨時禁用約束
-- MySQL外鍵開關
SET FOREIGN_KEY_CHECKS = 0; -- 關閉
SET FOREIGN_KEY_CHECKS = 1; -- 開啟
-- SQL Server
ALTER TABLE 表名 NOCHECK CONSTRAINT ALL;
4. 約束設計建議
- 主鍵用無意義數字（自增ID/UUID），避免業(yè)務字段
- 慎用
ON DELETE CASCADE（避免誤刪連鎖反應） - CHECK約束優(yōu)先于應用層校驗（保證數據純凈性）
綜合實戰(zhàn)：學生管理系統(tǒng)
-- 學院表（父表）
CREATE TABLE Colleges (
CollegeID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL UNIQUE
);
-- 學生表（子表）
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
IDCard CHAR(18) UNIQUE, -- 身份證唯一
CollegeID INT NOT NULL,
EnrollmentDate DATE DEFAULT GETDATE(),
GPA DECIMAL(3,2) CHECK (GPA BETWEEN 0 AND 4.0),
-- 表級外鍵（級聯更新）
CONSTRAINT fk_student_college
FOREIGN KEY (CollegeID)
REFERENCES Colleges(CollegeID)
ON UPDATE CASCADE
);
-- 課程表
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
Credit INT CHECK (Credit > 0) -- 學分需正數
);
-- 插入測試數據
INSERT INTO Colleges VALUES (1, '計算機學院');
INSERT INTO Students (StudentID, Name, CollegeID)
VALUES (1001, '張三', 1); -- 自動填充注冊日期
-- 錯誤測試：無效學院
INSERT INTO Students VALUES (1002, '李四', '310101...', 99, '2023-09-01', 3.8);
-- 報錯: Foreign key constraint violation
系統(tǒng)約束清單：
總結
SQL約束是數據庫的“守門員”，通過七類機制保障數據質量：
- CHECK：實現業(yè)務規(guī)則（如GPA范圍）
我們要合理使用SQL約束減少應用層校驗代碼，從根本上杜絕臟數據。
閱讀原文：原文鏈接
該文章在 2025/9/1 12:06:37 編輯過
400 186 1886
