SQL约束条件

哪吒 2024/1/1

# SQL约束条件

点击勘误issues (opens new window)，哪吒感谢大家的阅读

# 约束概述

SQL约束（Constraints）是数据库表中用于限制数据类型、格式和范围的规则。约束确保数据库中数据的准确性、一致性和可靠性。当违反约束条件时，数据库会拒绝相关操作并返回错误。

# 约束的作用

数据完整性 - 确保数据符合业务规则
数据一致性 - 维护数据之间的逻辑关系
数据质量 - 防止无效或错误数据的插入
业务规则 - 在数据库层面实施业务约束
性能优化 - 某些约束可以提高查询性能

# 约束类型

NOT NULL - 非空约束
UNIQUE - 唯一约束
PRIMARY KEY - 主键约束
FOREIGN KEY - 外键约束
CHECK - 检查约束
DEFAULT - 默认值约束
AUTO_INCREMENT - 自增约束（MySQL特有）

# NOT NULL约束

# 基本语法

-- 创建表时定义NOT NULL约束
CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),  -- 允许为空
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

# 实际应用示例

-- 用户注册表
CREATE TABLE user_registration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名，必填',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希，必填',
    email VARCHAR(100) NOT NULL COMMENT '邮箱，必填',
    first_name VARCHAR(30) NOT NULL COMMENT '名字，必填',
    last_name VARCHAR(30) NOT NULL COMMENT '姓氏，必填',
    phone VARCHAR(20) COMMENT '电话，可选',
    birth_date DATE COMMENT '生日，可选',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 正确插入
INSERT INTO user_registration (username, password_hash, email, first_name, last_name) VALUES
('john_doe', 'hashed_password_123', 'john@example.com', 'John', 'Doe');

-- 错误插入 - 违反NOT NULL约束
-- INSERT INTO user_registration (username, email, first_name, last_name) VALUES
-- ('jane_doe', 'jane@example.com', 'Jane', 'Doe');  -- 缺少password_hash

-- 查询验证
SELECT * FROM user_registration;

# 修改NOT NULL约束

-- 添加NOT NULL约束
ALTER TABLE user_registration 
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- 移除NOT NULL约束
ALTER TABLE user_registration 
MODIFY COLUMN phone VARCHAR(20) NULL;

-- 查看表结构
DESCRIBE user_registration;

# UNIQUE约束

# 单列唯一约束

-- 创建表时定义UNIQUE约束
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(20) UNIQUE NOT NULL,  -- 产品编码唯一
    product_name VARCHAR(100) NOT NULL,
    barcode VARCHAR(50) UNIQUE,  -- 条形码唯一，可为空
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO products (product_code, product_name, barcode, price) VALUES
('PROD001', '苹果手机', '1234567890123', 5999.00),
('PROD002', '华为手机', '1234567890124', 4999.00),
('PROD003', '小米手机', NULL, 2999.00);  -- barcode可为空

-- 尝试插入重复的product_code（会失败）
-- INSERT INTO products (product_code, product_name, price) VALUES
-- ('PROD001', '另一个产品', 1999.00);  -- 违反UNIQUE约束

SELECT * FROM products;

# 多列唯一约束

-- 复合唯一约束
CREATE TABLE user_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT,
    
    -- 同一用户不能有相同的角色
    UNIQUE KEY uk_user_role (user_id, role_id)
);

-- 插入测试数据
INSERT INTO user_roles (user_id, role_id, assigned_by) VALUES
(1, 1, 100),  -- 用户1分配角色1
(1, 2, 100),  -- 用户1分配角色2
(2, 1, 100);  -- 用户2分配角色1

-- 尝试插入重复组合（会失败）
-- INSERT INTO user_roles (user_id, role_id, assigned_by) VALUES
-- (1, 1, 101);  -- 用户1已经有角色1了

SELECT * FROM user_roles;

# 命名唯一约束

-- 显式命名约束
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(10) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    department_id INT,
    
    -- 命名约束便于管理
    CONSTRAINT uk_employee_id UNIQUE (employee_id),
    CONSTRAINT uk_employee_email UNIQUE (email),
    CONSTRAINT uk_employee_phone UNIQUE (phone)
);

-- 查看约束信息
SHOW CREATE TABLE employees;

-- 删除约束
ALTER TABLE employees DROP INDEX uk_employee_phone;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT uk_employee_phone UNIQUE (phone);

# PRIMARY KEY约束

# 单列主键

-- 方式1：在列定义时指定
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    parent_id INT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 方式2：在表定义末尾指定
CREATE TABLE brands (
    id INT AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    country VARCHAR(50),
    founded_year YEAR,
    website VARCHAR(100),
    
    PRIMARY KEY (id)
);

INSERT INTO categories (name, description, sort_order) VALUES
('电子产品', '各类电子设备', 1),
('服装', '男女服装', 2),
('图书', '各类书籍', 3);

INSERT INTO brands (name, country, founded_year, website) VALUES
('苹果', '美国', 1976, 'https://www.apple.com'),
('华为', '中国', 1987, 'https://www.huawei.com');

SELECT * FROM categories;
SELECT * FROM brands;

# 复合主键

-- 订单明细表使用复合主键
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_rate DECIMAL(5,4) DEFAULT 0.0000,
    total_price DECIMAL(10,2) NOT NULL,
    
    -- 复合主键：订单ID + 产品ID
    PRIMARY KEY (order_id, product_id)
);

-- 地区编码表
CREATE TABLE region_codes (
    country_code CHAR(2) NOT NULL,
    region_code CHAR(3) NOT NULL,
    region_name VARCHAR(100) NOT NULL,
    population INT,
    area_km2 DECIMAL(10,2),
    
    PRIMARY KEY (country_code, region_code)
);

-- 插入测试数据
INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price) VALUES
(1001, 1, 2, 5999.00, 11998.00),
(1001, 2, 1, 4999.00, 4999.00),
(1002, 1, 1, 5999.00, 5999.00);

INSERT INTO region_codes (country_code, region_code, region_name, population) VALUES
('CN', 'BJ', '北京市', 21540000),
('CN', 'SH', '上海市', 24280000),
('US', 'CA', '加利福尼亚州', 39510000);

SELECT * FROM order_items;
SELECT * FROM region_codes;

# 主键约束管理

-- 查看主键信息
SHOW INDEX FROM categories WHERE Key_name = 'PRIMARY';

-- 删除主键（需要先删除AUTO_INCREMENT）
-- ALTER TABLE test_table MODIFY id INT;
-- ALTER TABLE test_table DROP PRIMARY KEY;

-- 添加主键
-- ALTER TABLE test_table ADD PRIMARY KEY (id);

# FOREIGN KEY约束

# 基本外键约束

-- 创建父表
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    manager_id INT,
    budget DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建子表
CREATE TABLE employees_fk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT,
    salary DECIMAL(10,2),
    hire_date DATE NOT NULL,
    
    -- 外键约束
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入父表数据
INSERT INTO departments (name, budget) VALUES
('技术部', 1000000.00),
('销售部', 800000.00),
('人事部', 500000.00);

-- 插入子表数据
INSERT INTO employees_fk (employee_id, first_name, last_name, email, department_id, salary, hire_date) VALUES
('EMP001', '张', '三', 'zhangsan@company.com', 1, 15000.00, '2023-01-15'),
('EMP002', '李', '四', 'lisi@company.com', 1, 12000.00, '2023-02-01'),
('EMP003', '王', '五', 'wangwu@company.com', 2, 10000.00, '2023-03-01');

-- 尝试插入无效的department_id（会失败）
-- INSERT INTO employees_fk (employee_id, first_name, last_name, email, department_id, salary, hire_date) VALUES
-- ('EMP004', '赵', '六', 'zhaoliu@company.com', 999, 8000.00, '2023-04-01');

-- 查询结果
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.name as department_name,
    e.salary
FROM employees_fk e
LEFT JOIN departments d ON e.department_id = d.id;

# 外键约束选项

-- 带有级联操作的外键
CREATE TABLE orders_fk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
);

CREATE TABLE order_items_fk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    
    -- 外键约束with级联删除
    FOREIGN KEY (order_id) REFERENCES orders_fk(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    
    UNIQUE KEY uk_order_product (order_id, product_id)
);

-- 插入测试数据
INSERT INTO orders_fk (order_number, customer_id, order_date, total_amount) VALUES
('ORD20240001', 1001, '2024-01-15', 15998.00),
('ORD20240002', 1002, '2024-01-16', 4999.00);

INSERT INTO order_items_fk (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 5999.00),
(1, 2, 1, 4000.00),
(2, 1, 1, 4999.00);

-- 测试级联删除
DELETE FROM orders_fk WHERE id = 2;

-- 查看结果
SELECT * FROM orders_fk;
SELECT * FROM order_items_fk;

# 外键约束管理

-- 查看外键约束
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME,
    DELETE_RULE,
    UPDATE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'order_items_fk';

-- 删除外键约束
-- ALTER TABLE order_items_fk DROP FOREIGN KEY order_items_fk_ibfk_1;

-- 添加外键约束
-- ALTER TABLE order_items_fk 
-- ADD CONSTRAINT fk_order_items_order 
-- FOREIGN KEY (order_id) REFERENCES orders_fk(id) 
-- ON DELETE CASCADE ON UPDATE CASCADE;

# CHECK约束

# 基本CHECK约束

-- MySQL 8.0+ 支持CHECK约束
CREATE TABLE products_check (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_rate DECIMAL(5,4) DEFAULT 0.0000,
    stock_quantity INT NOT NULL DEFAULT 0,
    min_stock_level INT NOT NULL DEFAULT 0,
    category ENUM('electronics', 'clothing', 'books', 'food') NOT NULL,
    
    -- CHECK约束
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_discount_rate CHECK (discount_rate >= 0 AND discount_rate <= 1),
    CONSTRAINT chk_stock_non_negative CHECK (stock_quantity >= 0),
    CONSTRAINT chk_min_stock_level CHECK (min_stock_level >= 0),
    CONSTRAINT chk_stock_vs_min CHECK (stock_quantity >= min_stock_level OR min_stock_level = 0)
);

-- 正确插入
INSERT INTO products_check (name, price, discount_rate, stock_quantity, min_stock_level, category) VALUES
('iPhone 15', 5999.00, 0.1000, 50, 10, 'electronics'),
('T恤衫', 99.00, 0.0500, 100, 20, 'clothing'),
('编程书籍', 79.00, 0.0000, 30, 5, 'books');

-- 尝试插入无效数据（会失败）
-- INSERT INTO products_check (name, price, stock_quantity, category) VALUES
-- ('无效产品', -100.00, 10, 'electronics');  -- 价格为负数

-- INSERT INTO products_check (name, price, discount_rate, category) VALUES
-- ('无效产品2', 100.00, 1.5, 'electronics');  -- 折扣率超过100%

SELECT * FROM products_check;

# 复杂CHECK约束

-- 员工信息表with复杂约束
CREATE TABLE employees_check (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(10) NOT NULL UNIQUE,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    birth_date DATE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    bonus DECIMAL(10,2) DEFAULT 0.00,
    department ENUM('HR', 'IT', 'Sales', 'Finance', 'Marketing') NOT NULL,
    position_level ENUM('Junior', 'Senior', 'Lead', 'Manager', 'Director') NOT NULL,
    
    -- 复杂CHECK约束
    CONSTRAINT chk_birth_date CHECK (birth_date >= '1950-01-01' AND birth_date <= CURDATE()),
    CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01' AND hire_date <= CURDATE()),
    CONSTRAINT chk_age_at_hire CHECK (DATEDIFF(hire_date, birth_date) >= 6570), -- 至少18岁
    CONSTRAINT chk_salary_range CHECK (salary >= 3000 AND salary <= 100000),
    CONSTRAINT chk_bonus_vs_salary CHECK (bonus <= salary * 0.5), -- 奖金不超过工资的50%
    CONSTRAINT chk_email_format CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_phone_format CHECK (phone IS NULL OR phone REGEXP '^[0-9+\-\s()]+$')
);

-- 插入有效数据
INSERT INTO employees_check (employee_id, first_name, last_name, email, phone, birth_date, hire_date, salary, bonus, department, position_level) VALUES
('EMP001', '张', '三', 'zhangsan@company.com', '+86-138-0013-8000', '1990-05-15', '2023-01-15', 15000.00, 3000.00, 'IT', 'Senior'),
('EMP002', '李', '四', 'lisi@company.com', '139-0013-9000', '1985-08-20', '2022-03-01', 25000.00, 5000.00, 'IT', 'Lead'),
('EMP003', '王', '五', 'wangwu@company.com', NULL, '1992-12-10', '2023-06-01', 8000.00, 0.00, 'Sales', 'Junior');

SELECT 
    employee_id,
    CONCAT(first_name, last_name) as full_name,
    email,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) as years_of_service,
    salary,
    bonus,
    department,
    position_level
FROM employees_check;

# DEFAULT约束

# 基本默认值

CREATE TABLE user_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    theme VARCHAR(20) DEFAULT 'light',
    language VARCHAR(10) DEFAULT 'zh-CN',
    timezone VARCHAR(50) DEFAULT 'Asia/Shanghai',
    notifications_enabled BOOLEAN DEFAULT TRUE,
    email_frequency ENUM('immediate', 'daily', 'weekly', 'never') DEFAULT 'daily',
    max_file_size_mb INT DEFAULT 10,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据（使用默认值）
INSERT INTO user_settings (user_id) VALUES (1001);

-- 插入数据（部分覆盖默认值）
INSERT INTO user_settings (user_id, theme, language) VALUES 
(1002, 'dark', 'en-US');

-- 插入数据（显式指定所有值）
INSERT INTO user_settings (user_id, theme, language, timezone, notifications_enabled, email_frequency, max_file_size_mb) VALUES
(1003, 'auto', 'ja-JP', 'Asia/Tokyo', FALSE, 'weekly', 50);

SELECT * FROM user_settings;

# 表达式默认值

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    operation ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    record_id INT NOT NULL,
    old_values JSON,
    new_values JSON,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 计算默认值
    log_date DATE DEFAULT (CURDATE()),
    log_year YEAR DEFAULT (YEAR(CURDATE())),
    log_month TINYINT DEFAULT (MONTH(CURDATE()))
);

-- 插入审计日志
INSERT INTO audit_log (table_name, operation, record_id, new_values, user_id, ip_address) VALUES
('users', 'INSERT', 1001, '{"name": "张三", "email": "zhangsan@example.com"}', 100, '192.168.1.100'),
('products', 'UPDATE', 2001, '{"price": 5999.00}', 101, '192.168.1.101');

SELECT 
    table_name,
    operation,
    record_id,
    user_id,
    created_at,
    log_date,
    log_year,
    log_month
FROM audit_log;

# AUTO_INCREMENT约束

# 基本自增

CREATE TABLE auto_increment_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据（不指定id）
INSERT INTO auto_increment_demo (name) VALUES
('第一条记录'),
('第二条记录'),
('第三条记录');

-- 查看自增值
SELECT * FROM auto_increment_demo;

-- 查看当前自增值
SHOW TABLE STATUS LIKE 'auto_increment_demo';

# 自增值管理

-- 设置自增起始值
CREATE TABLE custom_auto_increment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    description TEXT
) AUTO_INCREMENT = 1000;

INSERT INTO custom_auto_increment (code, description) VALUES
('CODE001', '第一个代码'),
('CODE002', '第二个代码');

SELECT * FROM custom_auto_increment;

-- 修改自增值
ALTER TABLE custom_auto_increment AUTO_INCREMENT = 2000;

INSERT INTO custom_auto_increment (code, description) VALUES
('CODE003', '第三个代码');

SELECT * FROM custom_auto_increment;

-- 获取最后插入的ID
INSERT INTO custom_auto_increment (code, description) VALUES
('CODE004', '第四个代码');

SELECT LAST_INSERT_ID() as last_id;

# 自增与业务编号结合

-- 订单表with自动生成订单号
CREATE TABLE orders_with_auto_number (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器自动生成订单号
DELIMITER //
CREATE TRIGGER tr_generate_order_number 
BEFORE INSERT ON orders_with_auto_number
FOR EACH ROW
BEGIN
    SET NEW.order_number = CONCAT('ORD', DATE_FORMAT(NEW.order_date, '%Y%m%d'), LPAD(NEW.id, 4, '0'));
END//
DELIMITER ;

-- 由于触发器中使用了NEW.id，我们需要另一种方法
-- 删除触发器，使用存储过程
DROP TRIGGER tr_generate_order_number;

-- 创建存储过程生成订单
DELIMITER //
CREATE PROCEDURE CreateOrder(
    IN p_customer_id INT,
    IN p_order_date DATE,
    IN p_total_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_order_id INT;
    DECLARE v_order_number VARCHAR(20);
    
    -- 插入订单
    INSERT INTO orders_with_auto_number (customer_id, order_date, total_amount, order_number) 
    VALUES (p_customer_id, p_order_date, p_total_amount, 'TEMP');
    
    -- 获取插入的ID
    SET v_order_id = LAST_INSERT_ID();
    
    -- 生成订单号
    SET v_order_number = CONCAT('ORD', DATE_FORMAT(p_order_date, '%Y%m%d'), LPAD(v_order_id, 4, '0'));
    
    -- 更新订单号
    UPDATE orders_with_auto_number 
    SET order_number = v_order_number 
    WHERE id = v_order_id;
    
    -- 返回订单信息
    SELECT * FROM orders_with_auto_number WHERE id = v_order_id;
END//
DELIMITER ;

-- 使用存储过程创建订单
CALL CreateOrder(1001, '2024-01-15', 5999.00);
CALL CreateOrder(1002, '2024-01-15', 3999.00);
CALL CreateOrder(1003, '2024-01-16', 1999.00);

SELECT * FROM orders_with_auto_number;

# 约束的组合使用

# 完整的用户表设计

CREATE TABLE users_complete (
    -- 主键，自增
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 用户基本信息
    username VARCHAR(30) NOT NULL UNIQUE COMMENT '用户名，唯一',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱，唯一',
    phone VARCHAR(20) UNIQUE COMMENT '手机号，唯一，可为空',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    
    -- 个人信息
    first_name VARCHAR(30) NOT NULL COMMENT '名字',
    last_name VARCHAR(30) NOT NULL COMMENT '姓氏',
    gender ENUM('M', 'F', 'O') DEFAULT 'O' COMMENT '性别',
    birth_date DATE COMMENT '生日',
    
    -- 账户状态
    status ENUM('active', 'inactive', 'suspended', 'deleted') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,
    
    -- 时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    
    -- 约束条件
    CONSTRAINT chk_birth_date CHECK (birth_date IS NULL OR birth_date <= CURDATE()),
    CONSTRAINT chk_username_length CHECK (CHAR_LENGTH(username) >= 3),
    CONSTRAINT chk_email_format CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_phone_format CHECK (phone IS NULL OR phone REGEXP '^[+]?[0-9\-\s()]+$'),
    
    -- 索引
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) COMMENT='用户表';

-- 插入测试数据
INSERT INTO users_complete (username, email, phone, password_hash, first_name, last_name, gender, birth_date) VALUES
('zhangsan', 'zhangsan@example.com', '+86-138-0013-8000', 'hashed_password_1', '三', '张', 'M', '1990-05-15'),
('lisi', 'lisi@example.com', '139-0013-9000', 'hashed_password_2', '四', '李', 'F', '1992-08-20'),
('wangwu', 'wangwu@example.com', NULL, 'hashed_password_3', '五', '王', 'M', '1988-12-10');

-- 更新用户状态
UPDATE users_complete SET email_verified = TRUE, last_login_at = CURRENT_TIMESTAMP WHERE username = 'zhangsan';

SELECT 
    id,
    username,
    email,
    CONCAT(first_name, last_name) as full_name,
    gender,
    status,
    email_verified,
    phone_verified,
    created_at,
    last_login_at
FROM users_complete;

# 电商订单系统约束设计

-- 商品表
CREATE TABLE products_ecommerce (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE COMMENT '商品SKU',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    brand_id INT UNSIGNED COMMENT '品牌ID',
    price DECIMAL(10,2) NOT NULL COMMENT '价格',
    cost_price DECIMAL(10,2) COMMENT '成本价',
    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存数量',
    min_stock_level INT UNSIGNED DEFAULT 0 COMMENT '最低库存',
    max_stock_level INT UNSIGNED DEFAULT 9999 COMMENT '最高库存',
    weight_grams INT UNSIGNED COMMENT '重量（克）',
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 约束条件
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_cost_price CHECK (cost_price IS NULL OR cost_price >= 0),
    CONSTRAINT chk_stock_levels CHECK (min_stock_level <= max_stock_level),
    CONSTRAINT chk_current_stock CHECK (stock_quantity <= max_stock_level),
    CONSTRAINT chk_weight CHECK (weight_grams IS NULL OR weight_grams > 0)
);

-- 订单表
CREATE TABLE orders_ecommerce (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    customer_id INT UNSIGNED NOT NULL COMMENT '客户ID',
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 金额信息
    subtotal DECIMAL(12,2) NOT NULL COMMENT '小计',
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '税费',
    shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '运费',
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '折扣金额',
    total_amount DECIMAL(12,2) NOT NULL COMMENT '总金额',
    
    -- 状态信息
    status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending',
    payment_status ENUM('unpaid', 'paid', 'partial_refund', 'full_refund') DEFAULT 'unpaid',
    
    -- 地址信息
    shipping_address JSON NOT NULL COMMENT '收货地址',
    billing_address JSON COMMENT '账单地址',
    
    -- 时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    shipped_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    
    -- 约束条件
    CONSTRAINT chk_amounts_positive CHECK (
        subtotal >= 0 AND 
        tax_amount >= 0 AND 
        shipping_fee >= 0 AND 
        discount_amount >= 0 AND 
        total_amount >= 0
    ),
    CONSTRAINT chk_total_calculation CHECK (
        total_amount = subtotal + tax_amount + shipping_fee - discount_amount
    ),
    CONSTRAINT chk_discount_not_exceed CHECK (discount_amount <= subtotal),
    CONSTRAINT chk_paid_at CHECK (paid_at IS NULL OR payment_status = 'paid'),
    CONSTRAINT chk_shipped_at CHECK (shipped_at IS NULL OR status IN ('shipped', 'delivered')),
    CONSTRAINT chk_delivered_at CHECK (delivered_at IS NULL OR status = 'delivered')
);

-- 订单明细表
CREATE TABLE order_items_ecommerce (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    sku VARCHAR(50) NOT NULL COMMENT '商品SKU快照',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称快照',
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL COMMENT '单价',
    discount_rate DECIMAL(5,4) DEFAULT 0.0000 COMMENT '折扣率',
    line_total DECIMAL(12,2) NOT NULL COMMENT '行总计',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    FOREIGN KEY (order_id) REFERENCES orders_ecommerce(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products_ecommerce(id),
    
    -- 唯一约束
    UNIQUE KEY uk_order_product (order_id, product_id),
    
    -- 检查约束
    CONSTRAINT chk_quantity_positive CHECK (quantity > 0),
    CONSTRAINT chk_unit_price_positive CHECK (unit_price > 0),
    CONSTRAINT chk_discount_rate_valid CHECK (discount_rate >= 0 AND discount_rate <= 1),
    CONSTRAINT chk_line_total CHECK (line_total = quantity * unit_price * (1 - discount_rate))
);

-- 插入测试数据
INSERT INTO products_ecommerce (sku, name, category_id, price, stock_quantity, min_stock_level) VALUES
('IPHONE15-128-BLACK', 'iPhone 15 128GB 黑色', 1, 5999.00, 50, 10),
('IPHONE15-256-BLUE', 'iPhone 15 256GB 蓝色', 1, 6999.00, 30, 5),
('MACBOOK-AIR-M2', 'MacBook Air M2', 2, 8999.00, 20, 3);

INSERT INTO orders_ecommerce (order_number, customer_id, subtotal, tax_amount, shipping_fee, total_amount, shipping_address) VALUES
('ORD202401150001', 1001, 12998.00, 1299.80, 0.00, 14297.80, 
 JSON_OBJECT('name', '张三', 'phone', '138-0013-8000', 'address', '北京市朝阳区xxx街道xxx号', 'zipcode', '100000'));

INSERT INTO order_items_ecommerce (order_id, product_id, sku, product_name, quantity, unit_price, line_total) VALUES
(1, 1, 'IPHONE15-128-BLACK', 'iPhone 15 128GB 黑色', 1, 5999.00, 5999.00),
(1, 2, 'IPHONE15-256-BLUE', 'iPhone 15 256GB 蓝色', 1, 6999.00, 6999.00);

-- 查询订单详情
SELECT 
    o.order_number,
    o.customer_id,
    o.order_date,
    o.status,
    o.payment_status,
    o.total_amount,
    oi.product_name,
    oi.quantity,
    oi.unit_price,
    oi.line_total
FROM orders_ecommerce o
JOIN order_items_ecommerce oi ON o.id = oi.order_id
WHERE o.id = 1;

# 约束管理和维护

# 查看约束信息

-- 查看表的所有约束
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME 
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE tc.TABLE_SCHEMA = DATABASE()
  AND tc.TABLE_NAME = 'users_complete'
ORDER BY tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME;

-- 查看CHECK约束详情
SELECT 
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();

# 约束的添加和删除

-- 创建测试表
CREATE TABLE constraint_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    salary DECIMAL(10,2)
);

-- 添加NOT NULL约束
ALTER TABLE constraint_demo MODIFY name VARCHAR(50) NOT NULL;

-- 添加UNIQUE约束
ALTER TABLE constraint_demo ADD CONSTRAINT uk_email UNIQUE (email);

-- 添加CHECK约束
ALTER TABLE constraint_demo ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65);
ALTER TABLE constraint_demo ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 添加DEFAULT约束
ALTER TABLE constraint_demo ALTER COLUMN salary SET DEFAULT 5000.00;

-- 查看表结构
SHOW CREATE TABLE constraint_demo;

-- 删除约束
ALTER TABLE constraint_demo DROP INDEX uk_email;
ALTER TABLE constraint_demo DROP CHECK chk_age;

-- 修改约束
ALTER TABLE constraint_demo ADD CONSTRAINT chk_age_new CHECK (age >= 16 AND age <= 70);

# 约束冲突处理

-- 创建测试表
CREATE TABLE conflict_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT CHECK (age >= 18),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入正常数据
INSERT INTO conflict_demo (username, email, age) VALUES
('user1', 'user1@example.com', 25),
('user2', 'user2@example.com', 30);

-- 处理唯一约束冲突
-- 方法1：使用INSERT IGNORE
INSERT IGNORE INTO conflict_demo (username, email, age) VALUES
('user1', 'user1@example.com', 28);  -- 会被忽略

-- 方法2：使用ON DUPLICATE KEY UPDATE
INSERT INTO conflict_demo (username, email, age) VALUES
('user1', 'user1@example.com', 28)
ON DUPLICATE KEY UPDATE age = VALUES(age);

-- 方法3：使用REPLACE
REPLACE INTO conflict_demo (username, email, age) VALUES
('user1', 'user1@example.com', 32);

-- 查看结果
SELECT * FROM conflict_demo;

-- 批量插入with冲突处理
INSERT INTO conflict_demo (username, email, age) VALUES
('user3', 'user3@example.com', 22),
('user4', 'user4@example.com', 27),
('user1', 'user1_new@example.com', 35)  -- username冲突
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    age = VALUES(age);

SELECT * FROM conflict_demo ORDER BY id;

# 性能影响和优化

# 约束对性能的影响

-- 创建性能测试表
CREATE TABLE performance_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    age INT,
    salary DECIMAL(10,2),
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加索引（提高约束检查性能）
CREATE INDEX idx_username ON performance_test(username);
CREATE INDEX idx_email ON performance_test(email);
CREATE INDEX idx_department ON performance_test(department_id);

-- 添加约束
ALTER TABLE performance_test ADD CONSTRAINT uk_username UNIQUE (username);
ALTER TABLE performance_test ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE performance_test ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65);
ALTER TABLE performance_test ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 批量插入测试数据
INSERT INTO performance_test (username, email, phone, age, salary, department_id)
SELECT 
    CONCAT('user', n) as username,
    CONCAT('user', n, '@example.com') as email,
    CONCAT('138', LPAD(n, 8, '0')) as phone,
    18 + (n % 47) as age,
    3000 + (n % 50) * 100 as salary,
    1 + (n % 10) as department_id
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + 1 n
    FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) c
) numbers
WHERE n <= 1000;

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'performance_test';

-- 分析查询性能
EXPLAIN SELECT * FROM performance_test WHERE username = 'user500';
EXPLAIN SELECT * FROM performance_test WHERE email = 'user500@example.com';
EXPLAIN SELECT * FROM performance_test WHERE age BETWEEN 25 AND 35;

# 约束优化建议

-- 1. 合理使用复合索引
CREATE TABLE optimized_constraints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date DATE NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped') DEFAULT 'pending',
    
    -- 复合唯一约束
    UNIQUE KEY uk_user_product_date (user_id, product_id, order_date),
    
    -- 复合索引支持多种查询
    INDEX idx_user_date (user_id, order_date),
    INDEX idx_product_status (product_id, status),
    
    -- 检查约束
    CONSTRAINT chk_quantity CHECK (quantity > 0),
    CONSTRAINT chk_price CHECK (unit_price > 0)
);

-- 2. 避免过度约束
-- 错误示例：过多的CHECK约束
/*
CREATE TABLE over_constrained (
    id INT PRIMARY KEY,
    value1 INT CHECK (value1 > 0),
    value2 INT CHECK (value2 > 0),
    value3 INT CHECK (value3 > 0),
    value4 INT CHECK (value4 > 0),
    value5 INT CHECK (value5 > 0),
    -- 太多约束会影响插入性能
    CHECK (value1 + value2 + value3 + value4 + value5 > 100),
    CHECK (value1 * value2 > value3),
    CHECK (value4 / value5 < 10)
);
*/

-- 正确示例：合并相关约束
CREATE TABLE well_constrained (
    id INT PRIMARY KEY,
    value1 INT NOT NULL,
    value2 INT NOT NULL,
    value3 INT NOT NULL,
    value4 INT NOT NULL,
    value5 INT NOT NULL,
    
    -- 合并约束条件
    CONSTRAINT chk_all_positive CHECK (
        value1 > 0 AND value2 > 0 AND value3 > 0 AND value4 > 0 AND value5 > 0
    ),
    CONSTRAINT chk_business_rules CHECK (
        value1 + value2 + value3 + value4 + value5 > 100 AND
        value1 * value2 > value3 AND
        value4 / value5 < 10
    )
);

# 总结

# 约束使用最佳实践

数据完整性优先
- 在数据库层面实施约束，而不仅仅依赖应用层
- 使用适当的约束确保数据质量
性能考虑
- 为约束相关的列创建索引
- 避免过度复杂的CHECK约束
- 合理使用外键约束
命名规范
- 使用有意义的约束名称
- 遵循一致的命名规范
文档化
- 为约束添加注释说明业务含义
- 维护约束变更的文档
测试验证
- 测试约束在各种场景下的行为
- 验证约束冲突的处理逻辑

# 常见约束模式

用户表: NOT NULL + UNIQUE + CHECK(email格式)
订单表: 外键 + CHECK(金额) + 状态枚举
库存表: CHECK(数量) + 触发器维护一致性
审计表: 时间戳 + 不可修改约束

# 下一步学习

数据类型 DDL数据定义语言

Jeskson-微服务分布式

SQL约束条件

SQL约束条件

# SQL约束条件

# 约束概述

# 约束的作用

# 约束类型

# NOT NULL约束

# 基本语法

# 实际应用示例

# 修改NOT NULL约束

# UNIQUE约束

# 单列唯一约束

# 多列唯一约束

# 命名唯一约束

# PRIMARY KEY约束

# 单列主键

# 复合主键

# 主键约束管理

# FOREIGN KEY约束

# 基本外键约束

# 外键约束选项

# 外键约束管理

# CHECK约束

# 基本CHECK约束

# 复杂CHECK约束

# DEFAULT约束

# 基本默认值

# 表达式默认值

# AUTO_INCREMENT约束

# 基本自增

# 自增值管理

# 自增与业务编号结合

# 约束的组合使用

# 完整的用户表设计

# 电商订单系统约束设计

# 约束管理和维护

# 查看约束信息

# 约束的添加和删除

# 约束冲突处理

# 性能影响和优化

# 约束对性能的影响

# 约束优化建议

# 总结

# 约束使用最佳实践

# 常见约束模式

# 下一步学习