SQL常见约束及外键约束删除更新行为

一.约束

1.概念

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2.目的：

保证数据库中数据的正确、有效性和完整性。

3.添加约束两种方式

可以在创建表/修改表的时候添加约束。

在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可

二.约束分类

SQL中的约束用于确保数据库中数据的完整性、唯一性和一致性。常见的SQL约束分类包括：

1.非空约束（NOT NULL）

指定该字段不能为空。

- 例如，在创建学生表时，将学生ID和姓名字段设置为非空，以确保每个学生都有一个唯一的标识符和一个名字。

CREATE TABLE Students (
    StudentID int NOT NULL,
    Name varchar(255) NOT NULL
);

2. 默认约束（DEFAULT）

指定一个默认值来设置默认约束。
例如，在创建员工表时，将入职日期字段设置为默认值为'2023-01-01'，如果没有提供入职日期，则默认使用这个日期。

CREATE TABLE Employees (
    EmployeeID int NOT NULL AUTO_INCREMENT,
    JoinDate date DEFAULT '2023-01-01'
);

3. 唯一约束（UNIQUE）

指定该字段的值必须是唯一的，不允许重复。
例如，在创建用户表时，将电子邮件地址字段设置为唯一，以确保每个用户的电子邮件地址都是唯一的。

CREATE TABLE Users (
    UserID int NOT NULL AUTO_INCREMENT,
    Email varchar(255) UNIQUE
);

4. 主键约束（PRIMARY KEY）

指定表中的某个字段或字段组合必须是唯一的
例如，在创建订单表时，将订单ID字段设置为主键，以确保每个订单都有一个唯一的标识符。

CREATE TABLE Orders (
    OrderID int NOT NULL AUTO_INCREMENT,
    CustomerID int,
    PRIMARY KEY (OrderID)
);

注意：

当指定一个字段为主键时，该字段必须是非空且唯一的主键的主要特点如下：唯一性：主键的值必须是唯一的，这意味着在任何时候，表中都不能有两行具有相同的主键值。这保证了每一行数据都能被唯一地区分和识别。非空：主键字段不允许接受空值（NULL）。这是为了确保每一行都可以通过主键字段进行标识和访问。

5. 外键约束（FOREIGN KEY）

用于建立两个表之间的链接，指向另一个表的主键。
添加`FOREIGN KEY`关键字并指定引用的主键字段来设置外键约束。
外键用于建立两个表之间的链接，确保数据的一致性和完整性。
例如，在创建订单详情表时，将订单ID字段设置为外键，并引用订单表中的订单ID字段，以确保订单详情与相应的订单相关联

CREATE TABLE OrderDetails (
    OrderDetailID int NOT NULL,
    OrderID int,
    ProductID int,
    PRIMARY KEY (OrderDetailID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

6. 检查约束（CHECK）

检查约束用于限制字段的值必须满足特定的条件。

（注意：MySQL不支持CHECK约束的直接语法，但可以通过触发器实现）：

在其他支持CHECK约束的数据库中，可以这样创建：

在创建表时，通过在字段后面添加`CHECK`关键字并指定一个逻辑表达式来设置检查约束。

例如，在创建产品表时，将价格字段设置为检查约束，确保价格大于0。

CREATE TABLE Products (
    ProductID int NOT NULL,
    Price decimal(10, 2) CHECK (Price > 0)
);

三.外键约束

1.什么是外键约束？

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

2.两种创建方式

1.创建表时创建外键约束

CREATE TABLE 表名( 字段名数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );

2.对存在的表创建外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

注意主表要有主键，才能顺利添加从表外键而外键关联的时候从表字段一定是关联和主表的主键，但是从表有没有主键则无关紧要

3.删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

4. 删除/更新行为

添加了外键之后，在删除更新父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种

1. RESTRICT /NO ACTION（限制）

如果在父表上有关联的子表记录存在，那么父表上的记录不能被删除或更新。这是默认的约束行为。

ALTER TABLE 子表 ADD CONSTRAINT fk_外键列 FOREIGN KEY (外键列) REFERENCES 父表(主键列) ON DELETE RESTRICT ON UPDATE RESTRICT; 
ALTER TABLE 子表 ADD CONSTRAINT fk_外键列 FOREIGN KEY (外键列) REFERENCES 父表(主键列) ON DELETE NO ACTION ON UPDATE NO ACTION;

2. CASCADE（级联）

当父表上的记录被删除或更新时，关联的子表记录也会被对应删除或更新。

ALTER TABLE 子表 ADD CONSTRAINT fk_外键列 FOREIGN KEY (外键列) REFERENCES 父表(主键列) ON DELETE CASCADE ON UPDATE CASCADE

3. SET NULL（设置为NULL）

当父表上的记录被删除或更新时，子表中的外键列会被设置为NULL。

ALTER TABLE 子表 ADD CONSTRAINT fk_外键列 FOREIGN KEY (外键列) REFERENCES 父表(主键列) ON DELETE SET NULL ON UPDATE SET NULL;

4. SET DEFAULT（设置为默认值）

当父表上的记录被删除或更新时，子表中的外键列会被设置为默认值。

ALTER TABLE 子表 ADD CONSTRAINT fk_外键列 FOREIGN KEY (外键列) REFERENCES 父表(主键列) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

秒客网