Mysql约束条件

约束条件

非空约束

非空约束的特点

  • 非空约束强制要求某列的值不能为 NULL，确保该列始终包含有效数据
  • 在某些业务场景中，某些列的值是必须的（例如用户名、订单号等），非空约束可以避免因缺失数据而导致的逻辑错误
  • 如果某列不允许 NULL 值，查询时可以省略对 NULL 值的检查，简化查询逻辑

定义非空约束

  1. 在创建表时定义非空约束，在 CREATE TABLE 语句中，可以在列定义后添加 NOT NULL 关键字来定义非空约束

    CREATE TABLE 表名 (
    列名 数据类型 NOT NULL,
    ...
);

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL, -- 非空约束
    email VARCHAR(100) NOT NULL    -- 非空约束
);

  2. 在修改表时添加非空约束

    • 在 ALTER TABLE 语句中，可以使用 MODIFY 或 CHANGE 子句为现有列添加非空约束，这两个语句不会保留原来列的约束条件

      ALTER TABLE 表名
MODIFY 列名 数据类型 NOT NULL;

ALTER TABLE users
MODIFY email VARCHAR(100) NOT NULL;

  3. 同理使用 MODIFY 或 CHANGE 子句为现有列删除非空约束

  4. 默认值

    • 如果某列定义了非空约束但没有默认值，插入数据时必须显式指定该列的值，否则 MySQL 会报错

    • 可以通过 DEFAULT 关键字为列指定默认值，避免插入数据时的错误

      CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- 默认值
);

与其他约束的关系

  • 主键列默认具有非空约束，因此不需要显式定义 NOT NULL

  • 唯一约束允许 NULL 值，但每个 NULL 值被视为唯一

  • 如果需要确保某列的值唯一且不为 NULL，可以同时定义唯一约束和非空约束

    CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL -- 唯一约束 + 非空约束
);

  • MySQL 8.0.16 及以上版本支持 CHECK 约束，可以与非空约束结合使用，进一步限制列的值

    CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0) -- 非空约束 + 检查约束
);

唯一约束

唯一约束的特点

  • 唯一约束强制要求某列或某组列的值必须唯一，避免重复数据的插入
  • 唯一约束会自动创建唯一索引，从而提高查询性能
  • 在某些业务场景中，某些列的值必须是唯一的（例如用户名、邮箱、身份证号等），唯一约束可以帮助实现这些业务规则

定义唯一约束

  1. 在创建表时定义唯一约束，在 CREATE TABLE 语句中，可以在列定义后添加 UNIQUE 关键字来定义唯一约束

    CREATE TABLE 表名 (
    列名 数据类型 UNIQUE,
    ...
);

CREATE TABLE users (
    username VARCHAR(50) UNIQUE, -- 唯一约束
    email VARCHAR(100) UNIQUE    -- 唯一约束
);

  2. 在创建表时定义复合唯一约束，如果需要对多个列的组合值进行唯一性约束，可以在所有列定义后使用 UNIQUE 关键字

    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    UNIQUE (列名1, 列名2)
);

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    product_id INT UNSIGNED,
    UNIQUE (user_id, product_id) -- 复合唯一约束
);

  3. 修改表时添加唯一约束

    • 在 ALTER TABLE 语句中，可以使用 ADD CONSTRAINT 子句为现有列添加唯一约束

      ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE (列名);

ALTER TABLE users
ADD CONSTRAINT uk_email UNIQUE (email); -- 添加唯一约束

  4. 删除唯一约束

    • 使用 MODIFY 或 CHANGE 子句不会删除唯一索引

    • 在 ALTER TABLE 语句中，可以使用 DROP INDEX 子句移除现有列的唯一约束

    • 删除唯一约束实际上就是删除唯一索引

      ALTER TABLE 表名
DROP INDEX 约束名;

ALTER TABLE users
DROP INDEX uk_email; -- 移除唯一约束

与其他约束的关系

  • 唯一约束允许 NULL 值，但每个 NULL 值被视为唯一

  • 如果某列允许 NULL 值，则可以插入多个 NULL 值

    CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE -- 唯一约束
);

INSERT INTO users (email)
VALUES (NULL), (NULL); -- 允许插入多个 NULL 值

  • 主键约束默认具有唯一约束，因此不需要显式定义 UNIQUE

  • 唯一约束会自动创建唯一索引，从而提高查询性能

  • 如果某列已经定义了唯一索引，则不能再定义唯一约束

  • 复合唯一约束要求多个列的组合值必须唯一，但单个列的值可以重复

  • 复合唯一约束会自动创建一个唯一类型的复合索引（也称为联合索引或多列索引）

  • 索引的列顺序直接影响查询优化。例如，对列 (a, b) 创建复合唯一索引时，查询条件仅包含 a 时会触发索引，但仅包含 b 时无法触发（遵循最左前缀原则）

主键约束

主键约束的特点

  • 主键约束确保表中每一行都有一个唯一的标识符，便于数据的查找和操作
  • 主键约束会自动创建主键索引，从而提高查询性能
  • 主键约束确保主键列的值唯一且不为 NULL，避免数据重复和缺失
  • 主键约束是外键约束的基础，用于建立表与表之间的关联

添加主键约束

  1. 在创建表时定义主键约束，在 CREATE TABLE 语句中，可以在列定义后添加 PRIMARY KEY 关键字来定义主键约束

    CREATE TABLE 表名 (
    列名 数据类型 PRIMARY KEY,
    ...
);

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键约束
    username VARCHAR(50)
);

  2. 定义复合主键，如果需要对多个列的组合值进行唯一性约束，可以在所有列定义后使用 PRIMARY KEY 关键字，复合主键中所有列的值都不能为null

    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    PRIMARY KEY (列名1, 列名2)
);

CREATE TABLE orders (
    order_id INT UNSIGNED,
    user_id INT UNSIGNED,
    PRIMARY KEY (order_id, user_id) -- 复合主键
);

  3. 在修改表时添加主键约束，在 ALTER TABLE 语句中，可以使用 ADD CONSTRAINT 子句为现有列添加主键约束

    ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY (列名);

ALTER TABLE users
ADD CONSTRAINT pk_id PRIMARY KEY (id); -- 添加主键约束

  4. 在修改表时移除主键约束，在 ALTER TABLE 语句中，可以使用 DROP PRIMARY KEY 子句移除现有表的主键约束

    • 使用 MODIFY 或 CHANGE 子句不会删除主键约束

      ALTER TABLE 表名
DROP PRIMARY KEY;

ALTER TABLE users
DROP PRIMARY KEY; -- 移除主键约束

与其他约束关系

  • 主键列的值必须唯一，不能重复，并且主键列的值不能为 NULL

  • 复合主键要求多个列的组合值必须唯一，但单个列的值可以重复

  • 通常将主键列定义为自增列（AUTO_INCREMENT），以便自动生成唯一的值

    CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 自增主键
    username VARCHAR(50)
);

  • 主键约束会自动创建主键索引，从而提高查询性能

  • 如果某列已经定义了主键索引，则不能再定义主键约束

外键约束

外键约束的特点

  • 外键约束确保某列的值必须存在于关联表的主键列中，避免无效数据的插入

  • 外键约束用于建立表与表之间的关联，通常用于实现一对多或多对多的关系

  • 外键约束可以指定 ON DELETE 和 ON UPDATE 操作，例如级联删除或更新

  • 外键约束可以指定 ON DELETE 和 ON UPDATE 操作：

    • CASCADE：级联删除或更新。
    • SET NULL：设置为 NULL。
    • RESTRICT：拒绝删除或更新。
    • NO ACTION：与 RESTRICT 相同
    CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    
    FOREIGN KEY (user_id) REFERENCES users(id)
    
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
    • user_id：这是外键，属于当前表的列
    • users(id)：这是被引用的主键，属于 users 表的 id 列

添加外键约束

  1. 在创建表时定义外键约束，在 CREATE TABLE 语句中，可以在列定义后添加 FOREIGN KEY 关键字来定义外键约束

    CREATE TABLE 表名 (
    列名 数据类型,
    ...
    FOREIGN KEY (列名) REFERENCES 关联表名(关联列名)
        [ON DELETE 操作]
        [ON UPDATE 操作]
);

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    FOREIGN KEY (user_id) REFERENCES users(id) -- 外键约束
);

  2. 定义复合外键，如果需要对多个列的组合值进行外键约束，可以在所有列定义后使用 FOREIGN KEY 关键字

    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    FOREIGN KEY (列名1, 列名2) REFERENCES 关联表名(关联列名1, 关联列名2)
);

CREATE TABLE order_items (
    order_id INT UNSIGNED,
    product_id INT UNSIGNED,
    FOREIGN KEY (order_id, product_id) REFERENCES orders(order_id, product_id) -- 复合外键
);

  3. 修改表时添加外键约束，在 ALTER TABLE 语句中，可以使用 ADD CONSTRAINT 子句为现有列添加外键约束

    ALTER TABLE 表名
ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 关联表名(关联列名);

ALTER TABLE orders
ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id); -- 添加外键约束

  4. 移除外键约束，在 ALTER TABLE 语句中，可以使用 DROP FOREIGN KEY 子句移除现有表的外键约束

    • 使用 MODIFY 或 CHANGE 子句修改列的定义时，如果该列上有外键约束，可能会受到影响
      • 如果修改后的列定义与外键约束兼容（例如，数据类型和长度不变或兼容），MySQL 会允许修改，并且外键约束不会被删除
      • 如果修改后的列定义与外键约束不兼容（例如，数据类型或长度不匹配），MySQL 会报错，拒绝修改
      • 如果需要修改列的定义，并且修改与外键约束不兼容，可以先删除外键约束，修改列后再重新添加外键约束
    ALTER TABLE 表名
DROP FOREIGN KEY 约束名;

ALTER TABLE orders
DROP FOREIGN KEY fk_user_id; -- 移除外键约束

与其他约束之间的关系

  • 外键列的值必须存在于关联表的主键列中

  • 关联表的主键列必须具有唯一约束（通常是主键约束）索引要求

    • 父表的被引用列（通常是主键或唯一键）必须有一个索引
      • 当在 外键列所在表中插入或更新字段 时，MySQL 需要快速检查主键列所在表中是否存在对应的值
      • 如果没有索引，MySQL 需要对主键列所在表表进行全表扫描，性能会非常差
    • 如果子表的外键列上没有索引，MySQL 会自动为其创建一个索引
      • MySQL 为外键列生成的索引通常是 普通索引（非唯一索引）
      • 如果外键列本身有唯一约束，则会生成唯一索引
      • 如果删除外键约束，MySQL 不会自动删除为外键列生成的索引
      • 如果需要删除索引，必须手动执行 DROP INDEX
      • 如果外键是组合键（由多个列组成），MySQL 会为这些列创建一个组合索引
      • 由于该列不在组合索引的最左位置，MySQL 会为 该列单独创建一个新的索引，以满足外键约束的要求
      • 如果一个列是组合索引的一部分，但不在最左位置，MySQL 不会认为该列上存在一个可以直接使用的单列索引
    • 如果子表的外键列上已经有一个索引（无论是单列索引还是组合索引），MySQL 会直接使用该索引，而不会创建新的索引

  • 外键列如果允许 NULL 值，但 NULL 值不会触发外键约束检查

  • 外键约束仅在 InnoDB 存储引擎中支持，如果使用 MyISAM 存储引擎，外键约束会被忽略

检查约束

检查约束的特点

  • 条件驱动：基于逻辑表达式（如 Age >= 18）验证数据有效性
  • 灵活性：支持单列或跨列组合条件（如订单总价=数量×单价且总价≥0）
  • 多约束支持：同一表可定义多个检查约束

添加检查约束

  1. 创建表时定义，在 CREATE TABLE 语句中，可以在列定义后添加 CHECK 关键字来定义检查约束

    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    CHECK (条件)
);

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Age INT CHECK (Age >= 18),
  Salary DECIMAL CHECK (Salary >= 0)
);

  2. 修改表时添加

    • 可使用modify或者change语句进行添加

    • 在 ALTER TABLE 语句中，可以使用 ADD CONSTRAINT 子句为现有列添加检查约束

      ALTER TABLE 表名
ADD CONSTRAINT 约束名 CHECK (条件);

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0); -- 添加检查约束

  3. 复合约束

    如果需要对多个列的组合值进行检查约束，可以在所有列定义后使用 CHECK 关键字，需要定义为表级约束

    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    CHECK (条件)
);

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quantity INT,
    price DECIMAL(10, 2),
    CHECK (quantity > 0 AND price > 0) -- 表级检查约束
);

删除检查约束

  • 在 ALTER TABLE 语句中，可以使用 DROP CONSTRAINT 子句移除现有表的检查约束

    ALTER TABLE 表名
DROP CONSTRAINT 约束名;

ALTER TABLE employees
DROP CONSTRAINT chk_salary; -- 移除检查约束

检查约束注意点

  • 检查约束在 MySQL 8.0.16 及以上版本中支持
  • 在 MySQL 8.0.16 之前，检查约束会被解析但不会生效
  • 检查约束的条件可以是任何有效的 SQL 表达式，包括函数
  • 条件表达式的结果必须为布尔值（TRUE 或 FALSE）
  • 如果检查约束的条件涉及 NULL 值，NULL 值会被视为 TRUE，即允许插入，比如age列要求值大于0，但是插入null的话可以插入成功

默认值约束

默认值约束的特点

  • 当向表中插入新记录时，若未显式指定该字段的值，数据库会自动使用预设的默认值填充
  • 自动填充：简化数据插入操作，减少重复输入
  • 兼容性：支持多种数据类型（如数值、字符串、日期等）
  • 灵活性：允许与其他约束（如非空约束、唯一性约束）结合使用
  • 当插入null值时插入值的本身就为null，不会使用默认值
  • 默认值约束可以用于实现业务规则，例如默认状态、默认时间戳等

添加默认值约束

  1. 创建表时添加，在创建表结构时，直接在字段定义中使用 DEFAULT 关键字指定默认值，语法适用于所有主流数据库（如 MySQL、Oracle、SQL Server 等）

    CREATE TABLE 表名 (
    列名 数据类型 DEFAULT 默认值,
    ...
);

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    -- 为字段设置默认值
    Gender VARCHAR(10) DEFAULT 'male',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP  -- 支持函数作为默认值
);

  2. 修改表时添加

    • 可使用modify或者change添加

    • 在 ALTER TABLE 语句中，可以使用 ALTER COLUMN 子句为现有列添加默认值约束

      ALTER TABLE 表名
ALTER COLUMN 列名 SET DEFAULT 默认值;

ALTER TABLE Users
ALTER COLUMN Gender SET DEFAULT 'male';

 ALTER TABLE users
 ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP; -- 添加默认值约束
 ```

  1. 移除默认值约束

    • 在 ALTER TABLE 语句中，可以使用 ALTER COLUMN 子句移除现有列的默认值约束

      ALTER TABLE 表名
ALTER COLUMN 列名 DROP DEFAULT;

ALTER TABLE users
ALTER COLUMN created_at DROP DEFAULT; -- 移除默认值约束

默认约束注意点

  • 默认值必须与列的数据类型兼容
  • 如果某列允许 NULL 值且未定义默认值，插入数据时未指定该列的值会插入 NULL
  • 默认值可以是动态的，例如 CURRENT_TIMESTAMP 表示当前时间戳
  • 如果某列定义了 NOT NULL 约束且未定义默认值，插入数据时必须显式指定该列的值

列级约束

列级约束的特点

  • 列级约束是数据库表中直接定义在单个列上的约束规则，用于限制该列的取值范围或数据特性
  • 与表级约束（作用于多列或表整体）不同，列级约束仅针对特定列，常见于单字段数据验证场景
  • 列级约束优先于表级约束执行
  • MySQL 不支持在列级约束中自定义约束名称

其他常见列级约束

  • AUTO_INCREMENT，自动为列生成唯一的递增值，通常用于主键列

    CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

  • COMMENT，为列添加注释，描述列的用途或含义

    CREATE TABLE employees (
    id INT PRIMARY KEY COMMENT '员工唯一标识',
    name VARCHAR(100) COMMENT '员工姓名'
);

  • UNSIGNED，确保列中的值只能是非负数

  • GENERATED COLUMN，生成列的值是基于其他列的值计算得出的

    CREATE TABLE products (
    price DECIMAL(10, 2),
    quantity INT,
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

表级约束

表级约束的特点

  • 表级约束是数据库中对整个表或多个列组合进行数据完整性限制的规则，与列级约束（仅作用于单个列）形成互补

  • 表级约束通常用于处理涉及多列的逻辑验证或关系定义，例如复合主键、跨列检查条件、外键关联等

  • 表级约束中无法直接添加非空约束，因为非空约束（NOT NULL）本质上是列级约束

  • 表级约束也可以不指定约束名称，使用自定义名称

    • 通过表级 CHECK 约束强制某列不允许为空值（需注意数据库兼容性）不建议

      CREATE TABLE Users (
    UserID INT,
    Username VARCHAR(50),
    -- 表级CHECK约束模拟非空限制 
    CONSTRAINT CHK_Username_NotNull CHECK (Username IS NOT NULL)
);

表级约束语法

  • 表级约束通常在 CREATE TABLE 或 ALTER TABLE 语句中定义

  • CONSTRAINT 关键字：可选，用于为约束命名。constraint_name：约束的名称，便于后续管理和修改

    CREATE TABLE table_name (
    column1 datatype [列级约束],
    column2 datatype [列级约束],
    ...
    [CONSTRAINT constraint_name] 表级约束
);

用处

  1. 复合主键约束，当主键由多个列共同组成时，必须使用表级约束定义。

    CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)  -- 表级主键约束 
);

  2. 外键约束,用于维护表之间的关联关系，需指定关联的主表和列

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DeptID INT,
    CONSTRAINT FK_Dept 
        FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

  3. 多列唯一性约束,确保多列组合值的唯一性。例如，用户表中姓名与手机号的组合不可重复

    CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Name VARCHAR(50),
    Phone VARCHAR(20),
    CONSTRAINT UQ_Name_Phone UNIQUE (Name, Phone)
);

  4. 跨列检查约束,验证多列间的逻辑关系。

    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Quantity INT,
    Price DECIMAL(10,2),
    Total DECIMAL(10,2),
    CONSTRAINT CHK_Total 
        CHECK (Total = Quantity * Price AND Total >= 0)
);

  5. INDEX（索引约束），为表中的列（或列组合）创建索引，以提高查询性能

    CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    INDEX (last_name, first_name)
);

  6. FULLTEXT（全文索引约束），为表中的列（或列组合）创建全文索引，用于全文搜索

    CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
);

在修改表时添加约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 表级约束;

ALTER TABLE Employees  
ADD CONSTRAINT FK_Department  
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID);

删除约束

ALTER TABLE Orders  
DROP CONSTRAINT CHK_Total;
posted @ 2025-03-17 21:29  QAQ001  阅读(61)  评论(0)    收藏  举报