数据完整性，各种约束，主外键与级联

1.认识约束

1.1 三种完整性

约束是保证数据库完整性的方法

1.1.1 实体完整性

这个要求主键字段不能为空或者重复的值，保证唯一

1.1.2 区域完整性

保证数据在有效范围内

1.1.3 参照完整性

保证数据库中相关联的表里数据的正确性

1.2 约束类型

1.2.1 主键约束PrimaryKey

保证非空或者唯一的方式，主键可以由多个列组成
[[多列组合主键示例]]
已经创建了由Pno, Cno共同为主键的表

---

试着对Ord表插入3条数据,观察哪些数据可以进入数据库，理解组合主键。

insert into Ord(Pno,Cno,Sdate,Sqty)

values('P2001','C2002','2022-3-18 ',8);

成功

insert into Ord(Pno,Cno,Sdate,Sqty)

values('P2001','C1001','202317-4-18',1);

成功

insert into Ord(Pno,Cno,Sdate,Sqty)

values('P2001','C2002','20196-5-9 ',3);

失败
得知仅当主键中的两个字段值全都相同时，才会遭到约束

1.2.2 外键约束ForeignKey

对相关联的两表使用外键约束后，能保证

1. 表2要使用外键字段时必须保证其在表一中存在
2. 表1试图删除主键某字段值时必须保证该数据没被表2使用

1.2.3 唯一约束Unique

和主键约束同样是为了保证数据唯一，区别如下

1. 表中可以设置多个唯一约束，但仅一个主键约束
2. 唯一约束允许空值

1.2.4 检查约束Check

保证数量在范围内

1.2.5 非空约束Not Null

顾名思义

2. 使用约束

2.1 约束语法

这难崩的约束语法属实给我干沉默了，可读性太差
和GPT聊了聊，终于是把 CONSTRAINT 子句搞明白了:
[[CONSTRAINT 子句示例]]
CONSTRAINT 子句用于在创建或修改表时定义约束。

基本语法

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    CONSTRAINT constraint_name constraint_type (column_name)
);

创建带有约束的表

1. 创建带有命名主键的表

在创建表时，可以使用 CONSTRAINT 子句来命名主键约束。这有助于以后更方便地引用和管理该约束。

CREATE TABLE customer (
    cno CHAR(15),
    cname VARCHAR(30),
    city VARCHAR(30),
    manager VARCHAR(10),
    tel CHAR(20),
    CONSTRAINT pk_customer_cno PRIMARY KEY (cno)
);

在这个示例中：

• CONSTRAINT pk_customer_cno 指定约束的名称为 pk_customer_cno。
• PRIMARY KEY (cno) 指定 cno 列为主键。

2. 创建带有唯一性约束的表

唯一性约束确保列中的所有值都是唯一的。

CREATE TABLE employee (
    emp_id INT,
    emp_name VARCHAR(50),
    emp_email VARCHAR(50),
    CONSTRAINT uq_emp_email UNIQUE (emp_email)
);

在这个示例中：

• CONSTRAINT uq_emp_email 指定约束的名称为 uq_emp_email。
• UNIQUE (emp_email) 指定 emp_email 列具有唯一约束。

3. 创建带有外键约束的表

外键约束用于维护表之间的参照完整性。

CREATE TABLE orders (
    order_id INT,
    customer_id CHAR(15),
    order_date DATE,
    CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customer (cno)
);

在这个示例中：

• CONSTRAINT fk_customer_id 指定约束的名称为 fk_customer_id。
• FOREIGN KEY (customer_id) REFERENCES customer (cno) 指定 customer_id 列为外键，引用 customer 表的 cno 列。

4. 创建带有检查约束的表

检查约束用于确保列中的值满足指定的条件。

CREATE TABLE product (
    product_id INT,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    CONSTRAINT chk_price CHECK (price > 0)
);

在这个示例中：

• CONSTRAINT chk_price 指定约束的名称为 chk_price。
• CHECK (price > 0) 指定检查约束，确保 price 列的值大于 0。

修改表以添加命名约束

可以使用 ALTER TABLE 语句来添加命名约束。

添加命名主键约束

ALTER TABLE customer
ADD CONSTRAINT pk_customer_cno PRIMARY KEY (cno);

添加命名唯一约束

ALTER TABLE employee
ADD CONSTRAINT uq_emp_email UNIQUE (emp_email);

添加命名外键约束

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customer (cno);

删除命名约束

可以使用 ALTER TABLE 语句来删除命名约束。

删除主键约束

ALTER TABLE customer
DROP CONSTRAINT pk_customer_cno;

删除唯一约束

ALTER TABLE employee
DROP CONSTRAINT uq_emp_email;

删除外键约束

ALTER TABLE orders
DROP CONSTRAINT fk_customer_id;

定义约束的时候，要是表中存在与约束相悖的数据，那么定义语句将报错

2.2 实操创建主键与删除主键

使用如下语句

• 在创建表的同时创建主键
• 为city设置默认值“厦门”（用户键入数据时候若没有提供city值，会自动取值厦门）

create table customer(

        cno char(15) PRIMARY KEY,

        cname varchar(30),

        city VARCHAR(30) DEFAULT '厦门',

        manager varchar(10),

        tel  char(20));

删除刚才创建的主键

ALTER TABLE

customer

DROP CONSTRAINT PK__customer_cno

刚才的操作并没有指定主键名称，所以我是在SMSS图形化界面中修改得到了主键名称为 PK__customer_cno （默认名称里不知为何有难崩的一串乱码，而且PK后面的下划线还是两个），才能完成删除操作

为了优雅地脱离图形化界面完成这个操作，我试图用T-SQL查阅主键名称，代码如下

SELECT name

FROM sys.key_constraints

WHERE parent_object_id = OBJECT_ID('customer')

AND type = 'PK';

这个后面跟着一个看不懂但也无关紧要的查询条件
罢了，我干脆在创建表的时候就把主键名称指定好得了

CREATE TABLE customer (

    cno CHAR(15),
    cname VARCHAR(30),
    city VARCHAR(30),
    manager VARCHAR(10),
    tel CHAR(20),
    
    CONSTRAINT pk_customer_cno PRIMARY KEY (cno)
);

修改刚开始创建表的语句，使其在创建表和主键的同时指定了主键名称为 pk_customer_cno

在后来的学习中发现，其实在SQL sever中有通过存储过程轻松查询各种约束详细信息的方法

查询表中所有约束的详细信息

sp_helpconstraint 表名

执行后能得到如下图的信息

可以说是一目了然

2.3 实操探究外键关系

创建表的同时添加外键

CREATE TABLE customer (
    cno CHAR(15) PRIMARY KEY,
    cname VARCHAR(30),
    city VARCHAR(30),
    manager VARCHAR(10),
    tel CHAR(20)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id CHAR(15),
    order_date DATE,
    CONSTRAINT fk_customer_id FOREIGN KEY (customer_id)
        REFERENCES customer (cno)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

关键字 REFERENCES 后面要列出主表（本例中主表为customer）从表则是orders.
ON DELETE CASCADE 和 ON UPDATE CASCADE 分别应用了删除级联关系和更新级联关系

不使用级联的情况

• 试图在主表中删除主表和从表关联共有的数据，SMSS会报错；
• 试图在从表中删除主表和从表关联共有的数据，删除成功，查询从表，发现数据确实被删除，而主表中相关数据却还在

使用级联情况下

• 试图向从表中添加主表外键不存在的字段值，报错（该从表同时关联了两个主表，添加数据时一个字段值在主表1中存在，另一个则在主表2中不存在）
  得出不能添加任意一个主表中不存在的数据
• 试图在从表中删除与主表有关联的数据，删除成功，验证发现从表数据确实没了，主表还在
• 试图在主表中删除与从表有关联的数据，删除成功，验证发现主表和从表数据都没了

结论

在创建外键约束时候用到 REFERRNCES 关键字，测试的结果也充分说明了什么是参考，这个词用得确实很精妙了