1. 数据库创建基础与核心约束解析
在数据库管理系统中，CREATE DATABASE和CREATE TABLE是最基础却至关重要的SQL语句。作为从业15年的DBA，我见过太多因忽视基础约束而导致的系统故障。今天我们就深入探讨这些语句的实际应用场景和避坑指南。
创建数据库不是简单地执行一句CREATE DATABASE就完事了。需要考虑字符集（推荐UTF8MB4）、排序规则（如utf8mb4_general_ci）、文件组配置等要素。特别是在SQL Server中，错误3414通常是由于数据库未正常关闭导致的，这时需要先执行ALTER DATABASE SET EMERGENCY再修复。
2. 表创建与约束实战详解
2.1 表结构设计黄金法则
CREATE TABLE语句的核心在于字段定义和约束声明。我总结的"三三制"原则：
- 每个表必须有主键（最好自增INT/BIGINT）
- 字段数量控制在30个以内（超过就考虑分表）
- 必须有create_time和update_time时间戳
典型创建示例：
CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL COMMENT '登录账号', password CHAR(60) NOT NULL COMMENT 'BCrypt加密', email VARCHAR(100) UNIQUE, status TINYINT DEFAULT 1 NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME ON UPDATE CURRENT_TIMESTAMP, INDEX idx_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;2.2 约束类型深度对比
| 约束类型 | 作用 | 使用场景 | 性能影响 |
|---|---|---|---|
| PRIMARY KEY | 唯一标识 | 所有表必备 | 自动创建聚集索引 |
| FOREIGN KEY | 参照完整性 | 关联关系 | 影响DML性能 |
| UNIQUE | 唯一性 | 业务唯一字段 | 需要额外索引 |
| CHECK | 值域检查 | 数据有效性 | 逐行验证 |
| NOT NULL | 非空 | 关键字段 | 无额外开销 |
特别注意：NOT NULL约束在MySQL 8.0+会参与查询优化，能提升约15%的查询性能
3. 高频错误与解决方案
3.1 主键冲突处理
当遇到"违反了PRIMARY KEY约束"错误时，我的应急方案：
- 检查是否自增列达到上限（INT最大21亿）
- 确认是否有重复数据插入
- 在SQL Server中可使用IDENT_CURRENT检查当前值
3.2 外键级联操作
删除父表数据时，推荐使用：
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE;3.3 NOT NULL陷阱
很多开发者会这样定义：
address VARCHAR(200) NOT NULL DEFAULT ''这实际上违背了NOT NULL的初衷。正确的做法应该是允许NULL，在业务层校验：
address VARCHAR(200) NULL COMMENT '用户地址'4. 高级约束技巧
4.1 条件唯一约束
实现"软删除"后的唯一性：
CREATE TABLE product ( id INT PRIMARY KEY, name VARCHAR(100), is_deleted TINYINT DEFAULT 0, UNIQUE KEY uk_name (name, is_deleted) );4.2 跨列CHECK约束
SQL Server中的高级用法：
CREATE TABLE employee ( salary DECIMAL(10,2), bonus DECIMAL(10,2), CONSTRAINT ck_income CHECK (salary + bonus < 1000000) );4.3 动态默认值
MySQL 8.0+支持函数默认值：
CREATE TABLE log ( id INT PRIMARY KEY, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, hash VARCHAR(64) DEFAULT (SHA2(CONCAT(id,create_time),256)) );5. 性能优化实践
5.1 索引与约束的平衡
- 主键自动创建聚集索引（InnoDB）
- 唯一约束会创建唯一索引
- 外键列必须手动创建索引（否则锁表）
5.2 约束检查时机
在事务中调整约束检查时机：
SET CONSTRAINT ALL DEFERRED; -- PostgreSQL SET FOREIGN_KEY_CHECKS=0; -- MySQL5.3 系统表约束查询
获取所有约束信息：
-- MySQL SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name = 'user'; -- SQL Server EXEC sp_helpconstraint 'user';6. 企业级设计规范
根据金融级项目经验，建议：
- 生产环境禁用FOREIGN KEY（改由程序保证）
- 所有字符串字段必须显式定义长度
- 金额使用DECIMAL(19,4)避免精度丢失
- 大字段（如TEXT）单独存表
- 禁用ENUM类型（改用TINYINT+字典表）
在云数据库环境中，还要特别注意：
- 跨AZ部署时外键性能下降明显
- 分布式数据库可能不支持某些约束
- 约束名称需要全局唯一（加前缀）
最后分享一个真实案例：某电商系统因未设置NOT NULL导致促销计算错误，造成百万损失。记住：约束不是限制，而是对数据的保护。
