【数据库系统概论】第5章 SQL语言基础：数据定义的艺术（DDL）

博主智算菩萨，专注于人工智能、Python编程、音视频处理及UI窗体程序设计等方向。致力于以通俗易懂的方式拆解前沿技术，从零基础入门到高阶实战，陪伴开发者共同成长。目前已开设五大技术专栏，累计发布多篇原创技术文章，深受读者好评。

📌 专栏导航

• 人工智能前沿知识（已更201篇）：深度剖析Transformer架构、生成式AI、强化学习、具身智能、神经符号系统、大模型及智能体（Agent）技术，系统性解析AI核心技术体系与前沿趋势。
• Python基础小白编程（已更232篇）：从零开始，以保姆式教程讲解变量、数据类型、流程控制、函数等核心语法，配有大量实战代码与避坑指南，真正做到学以致用。
• 机器学习与深度学习（125篇）：系统化拆解线性模型、决策树、随机森林、梯度提升树、神经网络等算法原理与工程实践，覆盖从公式推导到代码实现的全链路内容。
• 音频、图像与视频处理理论与实战（81篇）：涵盖FFmpeg多媒体处理、audio_shop开源工具、ComfyUI-WanVideoWrapper视频生成等实用技术，从基础操作到高级应用一应俱全。
• UI窗体程序设计实战（78篇）：深入讲解UI设计、动态窗体生成、游戏UI框架设计等实战技巧，提供从配置到编码的完整解决方案。
  智算菩萨，以代码为经，以算法为纬，在人工智能的星辰大海中，做你前行路上最可靠的导航者。本人最常用AI工具为AIGCBAR。

1974年，IBM圣何塞研究实验室的两位研究员Donald Chamberlin和Raymond Boyce坐在一间没有任何窗户的办公室里，面对着一堵写满数学符号的白板，试图回答一个看似简单却影响深远的问题：如何让非程序员也能方便地从数据库中获取数据？ 当时的数据库查询语言要么过于数学化（如关系代数），要么深陷于过程式编程的泥潭（如CODASYL的导航式查询）。Chamberlin和Boyce的目标清晰而大胆——创造一种像英语一样直观、又像数学一样精确的查询语言。他们将其命名为SEQUEL（Structured English Query Language，结构化英语查询语言），后来由于商标原因更名为SQL（Structured Query Language，结构化查询语言）。

谁也不曾想到，这个诞生于实验室白板上的语言，会在接下来的五十年里成长为数据库领域的"世界语"。根据Stack Overflow 2023年的年度开发者调查，SQL在程序员最常用的技术中位列第四，超过60%的专业开发者每周都在使用SQL。从运行在智能手机上的SQLite，到支撑全球电商巨头核心交易的分布式数据库系统，SQL如同关系模型的通用翻译器，将人类的业务需求转化为精确的数据操作指令。它的成功并非偶然——SQL的设计哲学完美诠释了什么叫"简洁即力量"：用几条接近自然语言的语句，就能完成需要数十行过程式代码才能实现的复杂数据操作。

理解SQL，特别是理解SQL中负责数据定义的**DDL（Data Definition Language，数据定义语言）**部分，是掌握关系数据库的基石。如果说数据库是一座精心设计的城市，那么DDL就是这座城市的规划蓝图——它定义了每一块土地的用途（数据类型）、每一条街道的名称和走向（表结构）、每一栋建筑的准入规则（约束条件），以及建筑之间的连接关系（外键关联）。没有DDL的严谨定义，后续所有的数据操作都将如同在没有地基的土地上建房，随时可能崩塌。

本章将从SQL的整体架构出发，深入剖析DDL的核心语句与机制，并通过一个完整的Python+SQLite实战项目，让读者亲身体验如何用代码构建一个结构严谨、约束完备的数据库系统。

1 SQL语言概览

1.1 SQL的演进：从实验室到全球标准

SQL的历史可以追溯到关系模型诞生的早期。1970年，Edgar F. Codd发表了那篇开创性的论文《A Relational Model of Data for Large Shared Data Banks》，奠定了关系数据库的理论基础。四年后的1974年，Chamberlin和Boyce在IBM的System R项目中完成了SQL的最初设计。System R本身是一个实验性的关系数据库管理系统，但它证明了关系模型在商业环境中的可行性，而SQL作为其查询接口，也随着System R的成功而声名鹊起。

1979年是一个分水岭。当时名不见经传的Relational Software公司（后来的Oracle Corporation）发布了Oracle V2，这是第一款商用的SQL数据库产品。几乎在同一时期，IBM也推出了自己的SQL产品SQL/DS。这两股商业力量将SQL从实验室推向了市场。随后的十年间，SQL经历了爆炸式的发展——1986年，美国国家标准学会（ANSI）发布了首个SQL标准（SQL-86）；1987年，国际标准化组织（ISO）采纳了这一标准，SQL正式成为国际标准语言。

标准制定并没有停止SQL的进化。相反，每一次标准的更新都反映了数据库技术在应对新需求时的演进方向。SQL-92标准引入了子查询和CASE表达式，极大地增强了查询的表达能力；SQL:1999将SQL扩展为了一种完整的程序设计语言，加入了触发器、递归查询和面向对象特性；SQL:2003引入了窗口函数（Window Function），这一功能在数据分析领域产生了革命性的影响；SQL:2016则正式将JSON支持纳入标准，标志着传统关系数据库与NoSQL世界的融合。每一次标准的迭代，都使得SQL这个已经五十岁的语言焕发出新的生命力。

1.2 SQL的语言组成：四大子系统的协作

SQL并非单一的语言，而是一个由多个子系统协同工作的语言家族。按照功能的不同，SQL语句通常被划分为四个主要类别，分别对应数据管理生命周期的不同阶段。

类别	英文全称	中文名称	核心语句	功能描述
DDL	Data Definition Language	数据定义语言	CREATE、ALTER、DROP、TRUNCATE	定义和修改数据库对象（表、索引、视图等）的结构，是本章的核心内容
DML	Data Manipulation Language	数据操纵语言	INSERT、UPDATE、DELETE	对表中的数据进行增、删、改操作，是数据库日常维护的主力军
DQL	Data Query Language	数据查询语言	SELECT	从数据库中检索数据，是SQL中使用频率最高的语句类别，占所有SQL操作的70%以上
DCL	Data Control Language	数据控制语言	GRANT、REVOKE	管理数据库的访问权限，控制用户和角色对数据的访问级别

这四类语言之间的关系可以用一条清晰的逻辑链来理解：DDL搭建舞台（定义结构），DML和DQL在舞台上表演（操作和查询数据），DCL负责管理入场门票（权限控制）。没有DDL定义的结构，DML和DQL将无从施展；没有DCL的权限管理，数据的安全性将无法保障。这四个子系统共同构成了SQL完整的能力谱系。

需要特别注意的是，上述分类是学术界和教学中的惯用分法，但在实际的标准文档中，SQL通常被划分为**核心SQL（SQL/Foundation）**和多个可选扩展模块。此外，不同数据库厂商在标准SQL的基础上各自扩展了方言（Dialect），如Oracle的PL/SQL、SQL Server的T-SQL、MySQL的存储过程语法等。这些方言在核心语法上保持了兼容性，但在高级特性和系统函数上存在差异，开发者在跨数据库平台迁移时需要特别留意这些差异。

1.3 SQL的核心特点：为什么SQL如此成功

SQL之所以能够成为数据库领域长盛不衰的标准语言，源于其设计中蕴含的三个核心特点，这些特点共同构成了SQL独特的技术魅力。

综合统一的语言设计是SQL的第一大特点。与许多编程语言需要学习数十种不同的语法结构不同，SQL用一套统一的语法框架覆盖了数据定义、数据操纵、数据查询和数据控制的全流程操作。用户不需要在DDL语句和DML语句之间切换思维模式，因为所有SQL语句都遵循相似的语法结构——关键字后跟对象名、再跟参数列表。这种设计极大地降低了学习曲线，使得数据库管理员、数据分析师和应用开发者都能使用同一套语言与数据库交互。

**高度非过程化（Non-procedural）**是SQL最鲜明的特性，也是它与传统编程语言最本质的区别。使用SQL时，用户只需要声明"想要什么结果"（What），而不需要告诉数据库"如何得到这个结果"（How）。例如，当用户写下SELECT * FROM students WHERE gpa > 3.5时，他只需要表达"找出所有GPA大于3.5的学生"这一意图，至于数据库是使用索引扫描还是全表扫描、是先过滤后投影还是先投影后过滤——这些执行层面的决策完全由查询优化器（Query Optimizer）自动完成。这种非过程化的设计将开发者从底层实现细节中解放出来，使其能够专注于业务逻辑的表达。

面向集合的操作方式是SQL的第三大特点。与过程式语言通过循环逐行处理数据的编程范式不同，SQL的操作对象是集合（Set）。一条UPDATE语句可以同时修改成千上万条记录，一条SELECT语句可以返回一个完整的结果集，而用户根本不需要编写任何循环代码。这种集合操作方式不仅在语义上更接近关系代数的数学本质，在实际执行中也能够充分利用数据库引擎的批量处理能力，获得比逐行处理高出数个数量级的性能表现。

2 数据定义语言DDL

数据定义语言（DDL）是SQL中负责"建筑蓝图"的语言子集。它管理着数据库中所有对象的元数据（Metadata）——即关于数据的数据，包括表的结构定义、列的数据类型、列与列之间的约束关系、索引的构建规则等。与DML操作会改变表中的数据内容不同，DDL操作改变的是数据本身的"形态"和"组织方式"。

2.1 DDL的核心语句：CREATE、ALTER、DROP

DDL的操作可以概括为三个核心动词：创建（CREATE）、修改（ALTER）、删除（DROP）。这三个动词几乎可以作用于数据库中的所有对象类型——数据库、表、索引、视图、触发器、存储过程等。

CREATE语句是DDL中使用频率最高的语句，它负责从零开始构建数据库对象。CREATE DATABASE用于创建数据库实例，CREATE TABLE用于定义表结构，CREATE INDEX用于构建索引以加速查询，CREATE VIEW用于创建虚拟表以简化复杂查询。每一条CREATE语句的执行，都是在数据库的元数据目录（系统表）中添加新的定义条目，这些条目会被数据库引擎持久化存储，在数据库重启后依然有效。

ALTER语句用于修改已存在的数据库对象的结构。在表的生命周期中，业务需求的变化经常要求对表结构进行调整——增加新列以存储新增的属性、修改列的数据类型以支持更大的数值范围、重命名列以匹配新的命名规范。ALTER TABLE语句为这些场景提供了标准化的解决方案。需要特别注意的是，ALTER操作在大型表上的执行可能需要相当长的时间，因为某些修改（如改变列的数据类型）可能需要数据库重写整个表的数据页。

DROP语句用于彻底删除数据库对象。DROP TABLE不仅删除表的定义，还会删除表中存储的所有数据以及相关的索引和约束。这是一个不可逆的操作，一旦执行，数据将无法通过标准手段恢复（除非有备份）。因此，在生产环境中执行DROP操作之前，通常需要进行严格的变更审批流程和数据备份。

2.2 CREATE TABLE：构建数据的骨架

CREATE TABLE是DDL中最核心的语句，它定义了数据库中每一张表的完整结构。一条标准的CREATE TABLE语句包含以下关键组成部分：表名、列定义（列名、数据类型、约束）、表级约束（主键、外键、唯一约束等），以及可选的存储参数。

CREATE TABLE 表名 (
    列名1  数据类型  [列级约束],
    列名2  数据类型  [列级约束],
    列名3  数据类型  [列级约束],
    ...
    [表级约束1],
    [表级约束2],
    ...
);

从语法结构可以看出，CREATE TABLE语句的精髓在于约束的定义。约束是数据库完整性的守护者，它们定义了什么样的数据可以进入表中、数据之间应该满足什么样的关系、当数据发生变化时应该如何处理关联数据。约束的详细分类和定义方法将在5.3节中展开讨论。

2.3 数据类型：为每一列选择正确的"容器"

数据类型是CREATE TABLE语句中每个列定义不可或缺的组成部分。它为列中的每一个值规定了存储格式、取值范围和允许执行的操作。选择正确的数据类型不仅是编程规范的要求，更直接影响数据库的性能表现——不恰当的数据类型会导致存储空间的浪费、索引效率的下降，甚至在比较运算中引发隐式类型转换的性能损耗。

SQL标准定义了丰富的数据类型体系，各主流数据库在标准基础上又有所扩展。以下是常用数据类型的选择指南：

类别	数据类型	典型取值范围/说明	适用场景	选择建议
整数	INTEGER	-2147483648 至 2147483647（4字节）	自增主键、年龄、计数	这是数据库中最常用的整数类型，能覆盖绝大多数场景
	BIGINT	-9223372036854775808 至 9223372036854775807（8字节）	大数据量ID、时间戳微秒值	当INTEGER的20亿上限不够时使用
	SMALLINT	-32768 至 32767（2字节）	状态码、小范围枚举值	存储空间有限且数值范围确定时使用
定点数	DECIMAL(p,s) / NUMERIC(p,s)	精确存储p位有效数字，其中s位小数	金额、财务数据、精确计算	金额字段的首选，避免浮点数精度丢失
浮点数	REAL / FLOAT	单精度浮点，约6-7位有效数字	科学计算、传感器数据、近似值	需要快速计算且精度要求不高的场景
	DOUBLE	双精度浮点，约15-16位有效数字	复杂科学计算、统计分析	当REAL的精度不够时使用
字符串	CHAR(n)	固定长度n字符，不足补空格	定长编码（如学号、邮编）	长度固定时使用，存储效率更高
	VARCHAR(n)	可变长度，最大n字符	姓名、地址、描述性文本	字符串类型的默认选择，节省空间
	TEXT	超长文本，无明确上限	文章内容、日志、JSON字符串	大文本字段，不用于索引
日期时间	DATE	格式：YYYY-MM-DD	生日、纪念日、截止日期	仅需要日期部分时使用
	TIME	格式：HH:MM:SS	营业时间、时刻点	仅需要时间部分时使用
	DATETIME / TIMESTAMP	格式：YYYY-MM-DD HH:MM:SS	创建时间、更新时间、日志时间	需要同时记录日期和时间的场景
二进制	BLOB	二进制大对象	图片、音频、PDF文件	存储非结构化二进制数据
布尔	BOOLEAN	TRUE/FALSE（通常存储为1/0）	开关状态、是否标记	SQLite用INTEGER 0/1表示
JSON	JSON	JSON格式字符串	配置文件、动态属性、半结构化数据	PostgreSQL/MySQL原生支持，SQLite需配合TEXT

数据类型的选择应该遵循三个核心原则。第一，精确性原则：金额字段必须使用DECIMAL而非FLOAT，因为浮点数的精度问题在金融计算中会导致灾难性后果——例如0.1 + 0.2在浮点数中并不等于精确的0.3。第二，最小够用原则：在满足业务需求的前提下选择占用空间最小的类型。一个状态标志位使用BOOLEAN（1字节）而非INTEGER（4字节），在千万级数据量下能节省30MB的存储空间。第三，扩展性原则：为未来的业务增长预留空间。如果预计用户ID可能在两年内突破20亿，那么从一开始就应该选择BIGINT而非INTEGER。

3 完整性约束的定义

完整性约束（Integrity Constraints）是关系数据库的灵魂所在。如果数据类型定义了"列能放什么格式的数据"，那么完整性约束则定义了"列能放什么范围的数据"以及"列与列之间的关系"。Edgar F. Codd在设计关系模型时提出了三类完整性规则——实体完整性、参照完整性和用户定义完整性——而SQL的约束机制正是这三类完整性的工程实现。

3.1 主键约束（PRIMARY KEY）

实体完整性的核心要求是：关系（表）中的每一个元组（行）都必须是可唯一标识的。主键约束（PRIMARY KEY）就是实体完整性的守护者。一个表只能有一个主键，主键列的值不允许为NULL，也不允许重复。主键的作用如同每个人的身份证号码——它是表中每一行记录的唯一标识符。

主键可以定义在单个列上，也可以由多个列联合组成（复合主键）。在定义主键时，数据库引擎会自动为主键列创建唯一索引（Unique Index），以加速基于主键的查询和连接操作。这是一个重要的性能考量——查询条件中包含主键列的WHERE子句通常能够利用索引快速定位数据，而不是进行全表扫描。

-- 单列主键（列级定义）
student_id INTEGER PRIMARY KEY AUTOINCREMENT,

-- 复合主键（表级定义）
CONSTRAINT pk_sc PRIMARY KEY (student_id, course_id, semester)

主键的选择需要遵循一个基本原则：稳定性。理想的主键值一旦确定就不应该发生变化，因为主键值的变化会级联影响到所有引用它的外键关系。这就是为什么在数据库设计中，自增整数（AUTOINCREMENT）通常比业务相关的自然键（如学号、身份证号）更适合作为主键——自增整数完全由数据库管理，与业务逻辑无关，因此永远不会因为业务规则的变化而需要修改。

3.2 外键约束（FOREIGN KEY）

参照完整性确保了表与表之间关系的一致性。外键约束（FOREIGN KEY）定义了一个列（或列组合）的值必须引用另一个表中已存在的主键值。这种机制使得数据库能够自动维护父子表之间的关系，防止出现"悬空引用"——即子表中存在父表中不存在的引用值。

外键约束的威力不仅体现在数据验证上，更体现在其**级联操作（Referential Actions）**的能力上。当父表中的记录被更新或删除时，外键可以指定数据库应该如何处理依赖的子表记录。SQL标准定义了五种级联行为：

级联行为	触发条件	对子表的影响	典型应用场景
CASCADE	父记录被删除/更新	自动删除/更新子记录	选课记录（删除学生时自动清除其选课记录）
SET NULL	父记录被删除/更新	将子表的外键列设为NULL	部门员工关系（删除部门后员工归属变为待定）
SET DEFAULT	父记录被删除/更新	将子表的外键列设为默认值	默认分类（删除原分类后归入"未分类"）
RESTRICT	父记录被删除/更新	拒绝操作（如果有子记录存在）	防止误删（存在课程时禁止删除开课学院）
NO ACTION	父记录被删除/更新	与RESTRICT类似，但检查时机不同	标准SQL的默认行为

级联行为的选择需要结合具体的业务语义。以电商系统中的订单和订单明细为例——当用户删除一个订单时，其明细记录应该同步删除（ON DELETE CASCADE）；但当删除一个商品分类时，该分类下的商品不应该被自动删除，而是应该将其分类ID设为NULL（ON DELETE SET NULL）或者拒绝删除操作（ON DELETE RESTRICT）。错误的级联策略可能导致数据的意外丢失或业务逻辑的矛盾。

-- 外键约束定义示例
CONSTRAINT fk_student_dept 
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE SET NULL 
    ON UPDATE CASCADE

3.3 唯一约束（UNIQUE）

唯一约束（UNIQUE）确保一列（或列组合）中的所有值都是互不相同的。与主键约束不同，唯一约束允许列值为NULL（多个NULL值通常被视为互不重复），且一张表中可以定义多个唯一约束。唯一约束的典型应用场景包括：确保用户的邮箱地址不重复、确保商品的SKU编码不重复、确保订单编号不重复等。

当多个列联合定义唯一约束时，这种约束称为复合唯一约束。例如，在学生选课表中，(student_id, course_id, semester, academic_year)的复合唯一约束确保了同一个学生在同一个学期不能重复选修同一门课程——这是一种典型的业务规则在数据库层面的表达。

数据库引擎会为唯一约束自动创建唯一索引，这使得基于唯一约束列的查询也能享受到索引加速的好处。在实际应用中，唯一约束和唯一索引在功能上有很大的重叠，但语义上有所区别——唯一约束表达的是业务规则的约束，而唯一索引主要是出于性能优化的考虑。

3.4 检查约束（CHECK）

检查约束（CHECK）是SQL中最灵活的约束类型，它允许用户通过布尔表达式自定义数据的合法性条件。任何导致CHECK表达式返回FALSE的插入或更新操作都将被拒绝。CHECK约束是实现用户定义完整性的主要工具。

CHECK约束的强大之处在于其表达能力的丰富性。它可以实现数值范围检查（如CHECK (gpa >= 0.0 AND gpa <= 4.0)）、枚举值检查（如CHECK (gender IN ('M', 'F'))）、格式检查（如CHECK (email LIKE '%@%')）、以及跨列条件检查（如CHECK (end_date > start_date)）。

需要注意的是，不同数据库对CHECK约束的支持程度存在差异。SQLite和PostgreSQL对CHECK约束提供了全面的支持，表达式中甚至可以包含子查询。而MySQL在8.0.16版本之前虽然允许定义CHECK约束，但实际上会忽略它们（不执行任何验证），这是一个广为人知的历史遗留问题。因此，在MySQL 8.0.16之前的版本中使用CHECK约束时需要特别谨慎，可能需要借助触发器（Trigger）来实现类似的数据验证功能。

3.5 非空约束（NOT NULL）与默认值（DEFAULT）

非空约束（NOT NULL）是最基础也最重要的约束之一。它规定列的值不允许为NULL。在数据库设计中，每一列都应该被显式地标记为NOT NULL或NULL（允许为空），这是一个良好的设计习惯。不加思考地允许所有列接受NULL值，会导致后续查询中不得不处理大量的IS NULL判断，增加应用程序的复杂性。

默认值（DEFAULT）为非空约束提供了一个优雅的补充。当插入新记录时，如果没有为某列提供值，数据库会自动使用默认值填充。DEFAULT的常见用法包括：为created_at列设置默认值CURRENT_TIMESTAMP以自动记录创建时间、为状态列设置默认值'active'、为计数列设置默认值0。合理使用默认值可以简化应用程序的插入逻辑，同时确保数据的一致性。

3.6 命名约束

在定义约束时，为其指定一个有意义的名称（通过CONSTRAINT 约束名语法）是一个被许多开发者忽视的良好实践。当约束被违反时，数据库返回的错误信息中会包含约束名称。一个有意义的名称（如chk_gpa）比系统自动生成的名称（如sqlite_autoindex_students_1）更容易让开发者定位问题所在。在大型数据库系统中，约束命名规范甚至会成为编码规范的重要组成部分，确保团队成员能够通过约束名称迅速理解其业务含义。

4 Python实战：用sqlite3创建完整的数据库

理论的理解需要通过实践来深化。本节将通过一个完整的Python项目，演示如何使用Python标准库中的sqlite3模块创建一个结构严谨的学生-课程数据库。这个项目将综合运用前文介绍的所有DDL知识，包括四张表的创建、六种约束类型的定义、以及外键级联策略的配置。

4.1 项目设计：学生-课程数据库的ER模型

在动手编写代码之前，先对数据库的逻辑结构进行设计是必要的。本项目包含四张核心表：

departments（学院表）是数据的源头表之一，定义了学校下属的各个学院信息。students（学生表）和courses（课程表）分别引用departments的主键，表示学生和课程所属的学院。sc（选课表）是一个关联表（Junction Table），用于表达学生与课程之间的多对多关系——一个学生可以选修多门课程，一门课程也可以被多名学生选修。

4.2 完整代码：创建数据库与所有约束

import sqlite3

# 连接到SQLite数据库（如果不存在则自动创建）
conn = sqlite3.connect('university.db')
cursor = conn.cursor()

# SQLite默认关闭外键约束检查，必须显式启用
# 外键约束是参照完整性的保障，不启用时外键定义会被忽略
cursor.execute("PRAGMA foreign_keys = ON;")

# ============================================================
# 第一步：创建departments表（学院表）
# 这是"父表"之一，students表和courses表都将引用它
# ============================================================
cursor.execute('''
CREATE TABLE IF NOT EXISTS departments (
    -- dept_id：自增主键，AUTOINCREMENT确保ID单调递增不回收
    dept_id         INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- dept_name：学院全称，NOT NULL确保必有值，UNIQUE确保不重复
    dept_name       VARCHAR(50) NOT NULL UNIQUE,
    
    -- dept_code：3位学院代码，用CHAR(3)存储固定长度字符串
    dept_code       CHAR(3) NOT NULL UNIQUE,
    
    -- office_location：办公地点，允许NULL（新建学院可能未定）
    office_location VARCHAR(100),
    
    -- established_date：成立日期，DATE类型
    established_date DATE,
    
    -- budget：学院年度预算，DECIMAL精确存储金额，DEFAULT 0.00
    budget          DECIMAL(12, 2) DEFAULT 0.00,
    
    -- 命名约束：chk_dept_code 确保代码恰好3个字符
    CONSTRAINT chk_dept_code CHECK (LENGTH(dept_code) = 3),
    
    -- 命名约束：chk_budget 确保预算非负
    CONSTRAINT chk_budget CHECK (budget >= 0)
);
''')

# ============================================================
# 第二步：创建students表（学生表）
# 引用departments表作为外键，使用SET NULL级联策略
# ============================================================
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    -- 自增主键：student_id是代理键，与业务无关，永不修改
    student_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- student_no：学号，业务唯一标识
    student_no      CHAR(10) NOT NULL UNIQUE,
    
    -- name：姓名，NOT NULL
    name            VARCHAR(50) NOT NULL,
    
    -- gender：性别，CHECK约束限定只能为'M'或'F'，默认'M'
    gender          CHAR(1) DEFAULT 'M',
    
    -- birth_date：出生日期
    birth_date      DATE,
    
    -- enrollment_date：入学日期，默认当前日期
    enrollment_date DATE DEFAULT (DATE('now')),
    
    -- phone：手机号
    phone           VARCHAR(20),
    
    -- email：邮箱地址，CHECK约束确保包含@符号
    email           VARCHAR(100),
    
    -- dept_id：所属学院ID，外键引用departments
    dept_id         INTEGER,
    
    -- gpa：绩点，DECIMAL(3,2)精确到百分位，范围[0.00, 4.00]
    gpa             DECIMAL(3, 2) DEFAULT 0.00,
    
    -- status：学生状态，默认为'active'
    status          VARCHAR(10) DEFAULT 'active',
    
    -- 命名外键约束：删除学院时，该院学生的dept_id设为NULL
    CONSTRAINT fk_student_dept 
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    
    -- 命名检查约束：gpa在0到4之间
    CONSTRAINT chk_gpa CHECK (gpa >= 0.0 AND gpa <= 4.0),
    
    -- 命名检查约束：性别只能是M或F
    CONSTRAINT chk_gender CHECK (gender IN ('M', 'F')),
    
    -- 命名检查约束：邮箱必须包含@符号
    CONSTRAINT chk_email CHECK (email LIKE '%@%')
);
''')

# ============================================================
# 第三步：创建courses表（课程表）
# 同样引用departments表，但使用RESTRICT级联策略
# ============================================================
cursor.execute('''
CREATE TABLE IF NOT EXISTS courses (
    course_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    course_no   CHAR(8) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    credits     INTEGER NOT NULL DEFAULT 3,
    hours       INTEGER,
    dept_id     INTEGER,
    description TEXT,
    is_active   BOOLEAN DEFAULT 1,
    
    -- 外键约束：删除学院时，如果该院有课程则拒绝删除
    CONSTRAINT fk_course_dept 
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    
    -- 检查约束：学分必须在1到10之间
    CONSTRAINT chk_credits CHECK (credits > 0 AND credits <= 10)
);
''')

# ============================================================
# 第四步：创建sc表（选课表/关联表）
# 实现students与courses之间的多对多关系
# ============================================================
cursor.execute('''
CREATE TABLE IF NOT EXISTS sc (
    sc_id       INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id  INTEGER NOT NULL,
    course_id   INTEGER NOT NULL,
    semester    VARCHAR(10) NOT NULL,
    academic_year INTEGER NOT NULL,
    score       DECIMAL(5, 2),
    grade       VARCHAR(2),
    
    -- 外键约束：级联删除——学生退学时自动清除其所有选课记录
    CONSTRAINT fk_sc_student 
        FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    -- 外键约束：级联删除——课程停开时自动清除所有相关选课记录
    CONSTRAINT fk_sc_course 
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    -- 复合唯一约束：同一学生同一学期不能重复选同一门课
    CONSTRAINT uq_sc 
        UNIQUE (student_id, course_id, semester, academic_year),
    
    -- 检查约束：成绩必须在0到100之间
    CONSTRAINT chk_score CHECK (score >= 0 AND score <= 100)
);
''')

# 提交事务：确保所有DDL操作原子性地完成
conn.commit()
print("数据库创建成功！包含 departments、students、courses、sc 四张表")

# 关闭连接
conn.close()

4.3 代码解析：约束设计的工程考量

上述代码的约束设计体现了几个重要的工程考量。首先，主键全部采用自增整数（AUTOINCREMENT）作为代理键（Surrogate Key）。代理键完全由数据库自动生成，与业务数据无关，这意味着即使学号、课程编号等业务标识发生变化，表的主键也保持不变。这种设计避免了主键变更导致的级联更新风暴——在大型系统中，修改一条主键记录可能需要更新数百万条外键引用，这是一个极其危险的操作。

其次，外键的级联策略根据业务语义进行了差异化配置。对于students.dept_id → departments.dept_id关系，当学院被撤销时，该院学生的归属应该变为待定状态，因此选择ON DELETE SET NULL。对于courses.dept_id → departments.dept_id关系，如果学院还有在开的课程，那么不应该允许删除该学院，因此选择ON DELETE RESTRICT。对于sc表中的两个外键，学生退学或课程停开时，相关的选课记录自然失去意义，因此选择ON DELETE CASCADE。三种不同的级联策略，三种不同的业务语义——这正是数据库设计的艺术所在。

最后，所有约束都使用了命名约束（CONSTRAINT 名称），这使得在约束被违反时，错误信息能够清晰地指出哪个约束出了问题。例如，当试图插入GPA为4.5的记录时，数据库会返回CHECK constraint failed: chk_gpa——这比CHECK constraint failed这样模糊的提示要有用得多。

4.4 运行结果：数据库验证

运行上述代码后，数据库university.db被成功创建。以下是对数据库结构的验证输出：

数据库中的表 (4 个):
  1. courses
  2. departments
  3. sc
  4. students

表: departments
  列名               类型            可空      默认值        PK
  dept_id            INTEGER         NULL                  ✓
  dept_name          VARCHAR(50)     NOT NULL              
  dept_code          CHAR(3)         NOT NULL              
  office_location    VARCHAR(100)    NULL                  
  established_date   DATE            NULL                  
  budget             DECIMAL(12,2)   NULL     0.00         

外键约束验证:
  • courses.dept_id → departments.dept_id (更新: CASCADE, 删除: RESTRICT)
  • sc.student_id → students.student_id (更新: CASCADE, 删除: CASCADE)
  • sc.course_id → courses.course_id (更新: CASCADE, 删除: CASCADE)
  • students.dept_id → departments.dept_id (更新: CASCADE, 删除: SET NULL)

4.5 约束验证：六种约束的实战测试

为了验证约束是否按预期工作，我们向数据库中插入测试数据，并故意触发各类约束错误：

import sqlite3

conn = sqlite3.connect('university.db')
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

# === 插入正常数据 ===
departments = [
    ('计算机科学与技术学院', 'CST', '科技楼A座301', '1985-09-01', 5000000.00),
    ('数学科学学院', 'MAT', '理学楼B座202', '1952-06-15', 3200000.00),
    ('经济管理学院', 'ECO', '经管楼C座105', '1990-03-20', 4500000.00),
]
cursor.executemany(
    "INSERT INTO departments (dept_name, dept_code, office_location, "
    "established_date, budget) VALUES (?, ?, ?, ?, ?)",
    departments
)

students = [
    ('2024001001', '张明', 'M', '2002-05-15', '2024-09-01',
     '13800138001', 'zhangming@univ.edu.cn', 1, 3.75),
    ('2024001002', '李华', 'F', '2003-01-22', '2024-09-01',
     '13800138002', 'lihua@univ.edu.cn', 1, 3.50),
]
cursor.executemany(
    "INSERT INTO students (student_no, name, gender, birth_date, "
    "enrollment_date, phone, email, dept_id, gpa) VALUES "
    "(?, ?, ?, ?, ?, ?, ?, ?, ?)",
    students
)

conn.commit()

数据插入成功后，我们逐一触发各类约束错误来验证约束的有效性：

主键约束验证：尝试插入dept_id = 1的重复主键值，数据库拒绝操作并报告UNIQUE constraint failed: departments.dept_id。

唯一约束验证：尝试使用已存在的学号'2024001001'插入新学生，数据库拒绝并报告UNIQUE constraint failed: students.student_no。

非空约束验证：尝试将course_name设为NULL插入课程，数据库拒绝并报告NOT NULL constraint failed: courses.course_name。

检查约束验证：尝试插入gpa = 4.5（超出0-4范围）的学生，数据库拒绝并报告CHECK constraint failed: chk_gpa。同样，尝试插入credits = 15的课程，会触发CHECK constraint failed: chk_credits。

外键约束验证：尝试插入dept_id = 999（不存在的学院ID）的学生，数据库拒绝并报告FOREIGN KEY constraint failed。

复合唯一约束验证：尝试为同一个学生在同一学期重复选修同一门课程，数据库拒绝并报告UNIQUE constraint failed: sc.student_id, sc.course_id, sc.semester, sc.academic_year。

级联删除验证：删除一名有选课记录的学生后，查询其关联的选课记录数为0，验证ON DELETE CASCADE生效——删除学生自动清除了其所有选课记录。

SET NULL验证：删除一个被学生引用的学院后，查询该学生的dept_id字段值变为NULL，验证ON DELETE SET NULL生效。

5 表的修改与删除

数据库的结构并非一成不变。随着业务需求的演进，已经创建的表需要被修改——增加新列以存储新增的属性、修改列的定义以适应新的数据格式、删除不再使用的列以简化表结构。DDL提供了ALTER TABLE语句来完成这些结构性变更。

5.1 ALTER TABLE：修改表结构

ALTER TABLE语句提供了多种修改表结构的方式，但需要注意的是，不同数据库系统对ALTER TABLE的支持程度差异较大。SQLite作为轻量级数据库，支持的ALTER TABLE操作相对有限，而PostgreSQL和MySQL则提供了更丰富的修改能力。

**添加列（ADD COLUMN）**是ALTER TABLE最常用的操作。新添加的列默认位于表的最后位置，且不能定义为NOT NULL除非同时指定DEFAULT值（否则已存在的行将没有值可填充）。

-- 为学生表添加地址列
ALTER TABLE students ADD COLUMN address VARCHAR(200);

-- 添加带默认值的列（确保已有记录有值填充）
ALTER TABLE students 
ADD COLUMN emergency_contact VARCHAR(20) DEFAULT '未知';

**重命名列（RENAME COLUMN）**允许修改已有列的名称。这在重构数据库以匹配新的命名规范时特别有用。SQLite从3.25.0版本开始支持此操作。

-- 将phone列重命名为mobile_phone
ALTER TABLE students RENAME COLUMN phone TO mobile_phone;

**删除列（DROP COLUMN）**用于移除不再需要的列。SQLite从3.35.0版本开始支持此操作。在删除列之前，需要确保该列没有被其他数据库对象（如视图、触发器或外键约束）引用。

-- 删除不再使用的address列
ALTER TABLE students DROP COLUMN address;

**重命名表（RENAME TO）**用于修改表本身的名称。这个操作在SQLite中实现得较为简单，但需要注意：重命名表后，引用该表的外键约束和触发器定义可能需要相应更新。

-- 将sc表重命名为enrollments
ALTER TABLE sc RENAME TO enrollments;

5.2 索引的创建与删除

索引（Index）虽然不是通过ALTER TABLE创建的，但它是DDL的重要组成部分。索引通过额外的数据结构（通常是B+树）加速数据的查询，但会以增加存储空间和降低写入性能为代价。在经常用于查询条件、连接条件和排序操作的列上创建索引，是数据库性能优化的基本手段。

-- 在学生姓名上创建索引，加速按姓名查询
CREATE INDEX idx_students_name ON students(name);

-- 在成绩上创建降序索引，加速按成绩排序的查询
CREATE INDEX idx_sc_score ON sc(score DESC);

5.3 DROP TABLE：彻底删除表

DROP TABLE语句用于删除整个表——包括表的结构定义、所有数据、所有索引以及与该表相关的触发器。这是一个不可逆的操作，执行后数据将无法恢复。DROP TABLE的语法非常简单：

DROP TABLE IF EXISTS table_name;

IF EXISTS子句是一个安全网——如果表不存在，带IF EXISTS的语句会静默返回而不报错。在生产环境中执行DROP TABLE之前，必须确保：第一，已经备份了需要保留的数据；第二，没有其他表通过外键约束引用该表（或者已经处理了这些引用关系）；第三，应用程序代码中不再依赖该表的任何查询。

当试图删除一个被其他表引用的父表时，外键约束的行为取决于子表的定义。如果子表的外键定义了ON DELETE RESTRICT或ON DELETE NO ACTION，数据库将拒绝删除父表，直到所有子表记录被移除或外键约束被删除。如果子表定义了ON DELETE CASCADE，删除父表会级联删除所有子表记录——这是一个极其危险的操作，需要在设计阶段就充分考虑其影响。

6 域（DOMAIN）与断言（ASSERTION）

在深入掌握了DDL的核心语句之后，本节介绍两个SQL标准中定义的高级完整性机制——域（DOMAIN）和断言（ASSERTION）。这两个特性虽然在主流数据库中的支持情况不尽相同，但它们代表了对数据完整性进行更高级、更集中化管理的思想方向。

6.1 域（DOMAIN）：数据类型的封装与复用

**域（DOMAIN）**是SQL标准中定义的一种用户自定义数据类型，它允许将一个基本数据类型与一组约束条件打包在一起，形成可复用的数据定义单元。可以把域理解为一种"增强版的数据类型"——它不仅规定了数据的存储格式，还规定了数据必须满足的完整性条件。

域的核心价值在于集中化的约束管理。在一个实际的数据库系统中，同一种业务概念可能出现在多张表中。例如，email（邮箱地址）这一概念可能出现在students表、teachers表、admins表等多个表中。如果没有域，每一个表的email列都需要单独定义CHECK (email LIKE '%@%')约束。当业务规则发生变化（例如要求邮箱必须以.edu结尾）时，需要逐一修改每个表的约束定义——这是一个既繁琐又容易遗漏的过程。

域解决了这个问题。通过创建一个email_domain，所有使用这个域的列自动继承其约束条件。当需要修改验证规则时，只需修改域的定义即可，所有依赖该域的列会自动更新。

-- 创建域：定义邮箱数据类型及其验证规则
CREATE DOMAIN email_domain AS VARCHAR(100)
    CHECK (VALUE LIKE '%@%');

-- 创建域：定义绩点数据类型及其范围
CREATE DOMAIN gpa_domain AS DECIMAL(3, 2)
    CHECK (VALUE >= 0.0 AND VALUE <= 4.0)
    DEFAULT 0.00;

-- 在创建表时使用域
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    email      email_domain,       -- 自动继承域的约束
    gpa        gpa_domain          -- 自动继承域的约束和默认值
);

然而，域的支持在主流数据库中存在显著的差异。PostgreSQL提供了对CREATE DOMAIN的完整支持，是少数几个完全实现此特性的数据库之一。MySQL和SQLite则不支持CREATE DOMAIN语法。在不支持域的数据库中，开发者可以通过以下替代方案实现类似的效果：方案一是在应用程序层面对同一业务类型的字段进行统一的验证封装；方案二是在数据库中使用触发器（Trigger）来集中管理验证逻辑；方案三是利用存储过程或函数封装验证逻辑，并在CHECK约束中调用。

6.2 断言（ASSERTION）：跨表的完整性保障

如果说域和CHECK约束保护的是单表内部的数据完整性，那么断言（ASSERTION）则是SQL标准中定义的最强大的完整性机制——它能够保护跨表之间的数据完整性。

断言是一种数据库级别的约束，它定义了一个必须始终为真的布尔表达式。与CHECK约束只能在定义它的那张表的行被插入、更新或删除时触发不同，断言的验证时机更加全面——当任何可能影响断言表达式结果的操作发生时，数据库都会检查断言是否仍然成立。

考虑一个典型的业务场景：学校的选课人数不能超过课程容量。这个规则涉及两张表——sc（记录谁选了什么课）和假设存在的course_capacity（记录每门课的容量上限）。CHECK约束无法实现这种跨表的完整性检查，因为它只能引用本表的列。断言则可以优雅地解决这个问题：

-- SQL标准语法（注意：大多数数据库不支持ASSERTION）
CREATE ASSERTION course_capacity_check
    CHECK (
        NOT EXISTS (
            SELECT c.course_id, c.capacity, COUNT(sc.student_id) as enrolled
            FROM courses c
            JOIN course_capacity cc ON c.course_id = cc.course_id
            LEFT JOIN sc ON c.course_id = sc.course_id
            GROUP BY c.course_id, cc.capacity
            HAVING COUNT(sc.student_id) > cc.capacity
        )
    );

上述断言的含义是：数据库中不应该存在任何选课人数超过其容量的课程。每当有学生选课（向sc表插入记录）或课程容量被调低（修改course_capacity表）时，数据库都会自动检查这个条件，如果违反则拒绝操作。

遗憾的是，断言在主流数据库中几乎不被支持。截至当前，没有任何一个主流的生产级关系数据库管理系统实现了CREATE ASSERTION语法。这主要是因为断言的验证开销极大——任何对可能相关表的任何操作都需要重新评估所有断言，这在性能上几乎是不可接受的。在实际工程中，跨表的完整性保障通常通过触发器（Trigger）或应用程序逻辑来实现。触发器允许开发者在特定的数据操作（INSERT/UPDATE/DELETE）发生时执行自定义的验证代码，虽然不如断言声明式，但在功能上能够覆盖绝大多数断言的使用场景。

尽管如此，理解域和断言的概念仍然具有重要的理论价值。它们代表了SQL标准设计者对"声明式数据完整性"的理想追求——让数据库本身成为数据质量的最终守护者，而不是依赖每个应用程序开发者都不出错的自律。随着数据库技术的持续发展，或许在不久的将来，我们会看到这些高级完整性机制在生产环境中获得更广泛的支持。