SQL中怎么创建和删除约束

给已有数据的表添加约束，最头疼的不是语法，而是存量数据不合规导致的执行失败。比如想给 users 表的邮箱字段加唯一约束，却发现历史数据里有重复值；或者想给 orders 表加外键，结果发现一堆找不到对应用户的“孤儿订单”。

ALTER TABLE ... ADD CONSTRAINT 会在执行瞬间校验全表数据，任何一条记录违规都会导致事务回滚。因此，安全的操作顺序必须是：先查询定位脏数据，清理完毕后，再执行约束变更。下面以 PostgreSQL 语法为主线，穿插 MySQL 等数据库的差异点，拆解这一过程。

先检查旧数据再添加约束

在执行任何 ADD CONSTRAINT 之前，必须确认现有数据满足约束条件。我们以 users（用户表）和 orders（订单表）为例，常见的违规数据有三类：NULL 值、重复值和孤儿外键。

1. 检查 NULL 值

如果要为 users.email 添加 NOT NULL 约束，先找出空值：

SELECT id, email FROM users WHERE email IS NULL;

如果有结果，需要先决定是填充默认值、合并数据还是删除这些无效记录。

2. 检查重复值

添加 UNIQUE 约束前，必须确保没有重复。查找重复邮箱的常用写法：

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

3. 检查孤儿外键

给 orders.user_id 添加指向 users.id 的外键前，要确保每个订单都有合法的用户：

SELECT o.id, o.user_id 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;

先定位 NULL、重复值和孤儿外键，再执行迁移

如上图示意，在 IDE 或终端中，应先运行上述预检查询，确认结果集为空后，再进入下一步的约束添加环节。如果跳过这一步直接执行 ALTER TABLE，数据库会抛出类似 “violates unique constraint” 或 “foreign key constraint violation” 的错误，且不会告诉你具体是哪一行数据出了问题，排查成本极高。

用 ALTER TABLE 创建并命名约束

清理完数据后，就可以正式添加约束了。强烈建议为每个约束显式命名，而不是依赖数据库自动生成的随机名称（如 users_email_key 或 sys_c0012345）。命名规范通常采用 表名_字段名_约束类型 的格式，例如 users_email_unique。

添加 UNIQUE 约束

ALTER TABLE users 
ADD CONSTRAINT users_email_unique UNIQUE (email);

添加 CHECK 约束

假设要求用户年龄必须大于 0：

ALTER TABLE users 
ADD CONSTRAINT users_age_check CHECK (age > 0);

添加 FOREIGN KEY 约束

将订单关联到用户：

ALTER TABLE orders 
ADD CONSTRAINT orders_user_fk 
FOREIGN KEY (user_id) REFERENCES users(id);

为什么一定要命名？

1. 报错清晰：当插入违规数据时，错误信息会直接显示约束名，便于定位业务逻辑。
2. 迁移可控：在版本控制脚本中，明确的名称能避免不同环境生成不一致的约束名。
3. 删除方便：后续若要移除约束，必须知道确切名称。

删除约束时处理依赖和跨库语法

删除约束比创建更复杂，因为不同数据库的实现差异巨大，且可能存在依赖关系（如外键引用的索引）。

PostgreSQL 和 SQL Server

这两者遵循标准 SQL 语法，使用 DROP CONSTRAINT：

ALTER TABLE orders 
DROP CONSTRAINT orders_user_fk;

如果约束关联了索引（如唯一约束），PostgreSQL 会自动删除对应的索引。

MySQL 的特殊性

MySQL 没有通用的 DROP CONSTRAINT 语法（直到较新版本才部分支持），不同类型的约束删除方式不同：

• 删除外键：必须先删外键，再删可能存在的索引。

  ALTER TABLE orders DROP FOREIGN KEY orders_user_fk;
  -- 如果外键列上有自动创建的索引，可能需要额外删除索引
  ALTER TABLE orders DROP INDEX orders_user_fk; 
  

• 删除唯一/主键/检查约束：

  ALTER TABLE users DROP INDEX users_email_unique;
  ALTER TABLE users DROP PRIMARY KEY;
  

  注意：MySQL 中 UNIQUE 约束底层是通过唯一索引实现的，所以用 DROP INDEX。

SQLite 的限制

SQLite 对 ALTER TABLE 的支持非常有限。在旧版本中，它根本不支持直接删除约束。通常的做法是：

1. 创建一个新的临时表，结构中去掉该约束。
2. 将旧数据复制到新表。
3. 删除旧表，重命名新表。

命名约束、处理依赖、验证结构的变更流程

如上图流程所示，变更约束不仅仅是执行一条 SQL，更是一个包含“依赖检查、执行变更、验证结构”的完整迁移过程。特别是在生产环境，删除外键可能会影响级联删除行为，务必确认应用层逻辑已适配。

变更后的验证与上线顺序

执行完 ALTER TABLE 后，不要假设一切正常。应通过系统目录或描述命令验证约束是否生效。

验证方法

• PostgreSQL：使用 \d 命令查看表结构。

  \d users
  

  输出中应包含 Constraints 部分，列出刚才添加的约束名和类型。

• MySQL：使用 SHOW CREATE TABLE。

  SHOW CREATE TABLE orders;
  

  检查输出末尾的 CONSTRAINT 定义是否与预期一致。

• SQL Server：查询系统视图。

  SELECT name, type_desc 
  FROM sys.check_constraints 
  WHERE parent_object_id = OBJECT_ID('users');
  

上线建议

1. 小批量清理：如果脏数据量很大，不要一次性 UPDATE 或 DELETE，分批次进行，避免锁表过久。
2. 事务保护：在支持事务的数据库（如 PostgreSQL）中，将清理数据和添加约束放在同一个事务里。如果添加失败，清理操作也能回滚，保持数据状态一致。
3. 低峰期执行：ALTER TABLE 在大表上可能会触发表重建或长时间锁定，建议在业务低峰期进行，并提前评估锁行为。

通过“先查后改、明确命名、跨库适配、事后验证”这四步，你可以更安全地管理数据库约束，避免因结构变更引发的线上事故。

参考资料

PostgreSQL ALTER TABLE MySQL ALTER TABLE SQL Server ALTER TABLE SQLite ALTER TABLE