SQL约束详解：语法、应用与实战示例

内容分享3周前发布 念时ing时念______
1 0 0

SQL约束是数据库设计的核心机制，用于强制数据完整性和业务规则。以下按约束类型分类解析，包含基础语法、参数说明、应用场景和实战示例，仅供参考。

一、SQL约束的基础概念

作用：在数据插入/更新时自动校验，防止非法数据破坏完整性
分类：

  • 列级约束：直接定义在字段后（CREATE TABLE时）
  • 表级约束：独立声明在所有字段后（支持多字段组合约束）
    共性特征：
  • 约束名可自定义（提议约束类型_表名_字段格式）
  • 违反约束时操作被终止，抛出错误代码（如MySQL 1062主键冲突）
  • 支持CREATE TABLE或ALTER TABLE两种定义方式

数据完整性类型：

实体完整性（主键）

参照完整性（外键）

域完整性（非空/检查/默认值）

二、SQL约束类型详解

1. NOT NULL 约束

作用：禁止字段存储NULL值（空值 ≠ 0或空字符串）
语法：

-- 创建表时定义
CREATE TABLE 表名 (
    字段名 数据类型 NOT NULL
);

-- 修改表结构
ALTER TABLE 表名 
MODIFY 字段名 数据类型 NOT NULL;  -- MySQL/PostgreSQL
ALTER TABLE 表名 
ALTER COLUMN 字段名 SET NOT NULL; -- SQL Server

重大特性：

  • Oracle中NULL与空字符串等价
  • SQL Server严格区分NULL与''

实战示例：

-- 创建用户表
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50) NOT NULL,  -- 用户名不能为空
    BirthDate DATE NOT NULL         -- 出生日期必填
);

-- 插入非法数据（触发约束）
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 (字段);

跨数据库差异：

数据库

NULL处理规则

SQL Server

唯一索引中NULL视为相等（仅1个）

Oracle

全空字段组合允许重复

MySQL

允许多个NULL（不视为相等）

实战示例：

-- 创建员工邮箱表
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 (字段);

核心规则：

  • 每表仅一个主键（可多字段组合）
  • 主键字段自动创建唯一索引
  • MySQL中主键约束名固定为PRIMARY

实战示例：

-- 创建订单表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,     -- 列级主键
    OrderDate DATE NOT NULL
);

-- 创建订单详情（复合主键）
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 约束

作用：强制表间引用完整性（子表引用父表主键）
语法：

-- 创建表时定义
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

子表字段置为NULL

SET DEFAULT

子表字段置为默认值

实战示例：

-- 父表：部门
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 约束

作用：定义字段值的业务规则（类似WHERE条件）
语法：

-- 列级约束
CREATE TABLE 表名 (
    字段名 数据类型 CHECK (条件)
);

-- 表级多字段约束
CREATE TABLE 表名 (
    字段1 数据类型,
    字段2 数据类型,
    CONSTRAINT chk_name CHECK (字段1 > 字段2)
);

-- 添加约束
ALTER TABLE 表名 
ADD CONSTRAINT chk_name CHECK (条件);

特殊规则：

  • 检查结果为NULL时视为通过
  • 条件中可调用函数（如GETDATE()）
  • SQLite仅支持CREATE TABLE时定义

实战示例：

-- 创建账户表
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2) CHECK (Balance >= 0),  -- 余额不可负
    Status VARCHAR(10) CHECK (Status IN ('ACTIVE', 'CLOSED'))
);

-- 添加自定义规则（开户日期早于今日）
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 约束

作用：字段未赋值时自动填充默认值
语法：

-- 创建表时定义
CREATE TABLE 表名 (
    字段名 数据类型 DEFAULT 默认值
);

-- 修改默认值
ALTER TABLE 表名 
ALTER COLUMN 字段名 SET DEFAULT 值;  -- MySQL/PostgreSQL
ALTER TABLE 表名 
ADD CONSTRAINT 约束名 DEFAULT 值 FOR 字段;  -- SQL Server

常用默认值：

  • 固定值：DEFAULT 0
  • 系统函数：DEFAULT GETDATE()（当前时间）
  • 表达式：DEFAULT (UUID())（生成唯一ID）

实战示例：

-- 创建用户注册表
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自动创建）
语法：

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），避免业务字段
  • 外键字段需加索引（提升JOIN性能）
  • 慎用ON DELETE CASCADE（避免误删连锁反应）
  • CHECK约束优先于应用层校验（保证数据纯净性）

综合实战：学生管理系统

-- 学院表（父表）
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

系统约束清单：

学院名称全局唯一

学生身份证号唯一

GPA范围0~4.0

学院更新时自动同步到学生表

注册日期自动生成

总结

SQL约束是数据库的“守门员”，通过七类机制保障数据质量：

  1. NOT NULL：强制关键字段必填
  2. UNIQUE：防止重复数据（如身份证）
  3. PRIMARY KEY：确立数据唯一标识
  4. FOREIGN KEY：维护表间引用关系
  5. CHECK：实现业务规则（如GPA范围）
  6. DEFAULT：智能填充缺失值
  7. INDEX：幕后支持约束性能

我们要合理使用SQL约束减少应用层校验代码，从根本上杜绝脏数据。

© 版权声明
文章版权归作者所有，未经允许请勿转载。

相关文章

暂无评论

您必须登录才能参与评论！
立即登录
none
暂无评论...
头像
念时ing时念______
投稿者
1002
SQL约束详解：语法、应用与实战示例

热门网站

日榜周榜月榜
广告违禁词检测

广告违禁词检测

句易网 - 2025年最新广告法淘宝抖音违禁词敏感词在线查询检测工具
易飞文字识别

易飞文字识别

免费在线批量图片文字提取，支持中英文等多种语言的高效准确识别，一键复制文本导出Word。
Yandex站长平台

Yandex站长平台

Yandex ID. The key to all your...
下载之家

新下载之家

下载之家提供新鲜安全的绿色软件、系统软件下载服务.所有软件均由下载之家严格检测,无病毒木马、诱导广告.安全下载绿色软件就到下载之家.
单机游戏下载

新单机游戏下载

逗游网是的绿色单机游戏下载基地，提供单机游戏、网络游戏、好玩的手机游戏一键下载，自动安装，绿色安全！逗游网让您轻松玩游戏！
查看完整榜单

热门文章

日榜周榜月榜
什么是Vue 3 “Vapor Mode”(转)

什么是Vue 3 “Vapor Mode”(转)

头像3周前
1
决策升级：系统1挖的坑，系统2来填-《思考快与慢》笔记

新决策升级：系统1挖的坑，系统2来填-《思考快与慢》笔记

头像6天前
1
数据中台建设成本分析：人力+硬件+软件，中小公司预算指南

新数据中台建设成本分析：人力+硬件+软件，中小公司预算指南

头像4天前
1
Android手动命令行编译APK

新Android手动命令行编译APK

头像2天前
1
MATLAB App Designer学习之旅（一）

新MATLAB App Designer学习之旅（一）

头像2天前
1
我们都不卖 | 字节跳动正探索不出售TikTok美国业务的解决方案

新我们都不卖 | 字节跳动正探索不出售TikTok美国业务的解决方案

头像2天前
1
查看完整榜单

标签云