面试题：MySQL高级难度之复杂场景下的数据完整性保障

Question

假设你有一个电商数据库，其中包含订单表（orders）、订单详情表（order_items）和商品表（products）。订单表记录订单的基本信息，订单详情表记录每个订单包含的商品及数量，商品表记录商品的详细信息。为了确保数据完整性，在订单详情表中插入数据时，如何通过约束保证所引用的商品ID在商品表中存在，且订单中的商品数量不能为负数？请写出对应的SQL语句及必要的解释。

Answer 1

1. 在MySQL中：

   • 首先创建表结构并添加约束。

   -- 创建商品表
   CREATE TABLE products (
       product_id INT PRIMARY KEY,
       product_name VARCHAR(255),
       -- 其他商品相关字段
       --...
   );
   
   -- 创建订单表
   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       -- 其他订单相关字段
       --...
   );
   
   -- 创建订单详情表，并添加外键约束和检查约束
   CREATE TABLE order_items (
       order_item_id INT PRIMARY KEY,
       order_id INT,
       product_id INT,
       quantity INT,
       FOREIGN KEY (product_id) REFERENCES products(product_id),
       CHECK (quantity > 0),
       FOREIGN KEY (order_id) REFERENCES orders(order_id)
   );
   

   • 解释：
     • FOREIGN KEY (product_id) REFERENCES products(product_id)：这行代码在order_items表的product_id列上创建了一个外键约束，确保插入到order_items表中的product_id值必须在products表的product_id列中已经存在，从而保证了数据的引用完整性。
     • CHECK (quantity > 0)：这行代码创建了一个检查约束，确保插入到order_items表中的quantity值大于0，即商品数量不能为负数。

2. 在Oracle中：

   -- 创建商品表
   CREATE TABLE products (
       product_id NUMBER PRIMARY KEY,
       product_name VARCHAR2(255)
       -- 其他商品相关字段
       --...
   );
   
   -- 创建订单表
   CREATE TABLE orders (
       order_id NUMBER PRIMARY KEY
       -- 其他订单相关字段
       --...
   );
   
   -- 创建订单详情表，并添加外键约束和检查约束
   CREATE TABLE order_items (
       order_item_id NUMBER PRIMARY KEY,
       order_id NUMBER,
       product_id NUMBER,
       quantity NUMBER,
       CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(product_id),
       CONSTRAINT ck_quantity CHECK (quantity > 0),
       CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES orders(order_id)
   );
   

   • 解释：
     • CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(product_id)：通过CONSTRAINT关键字定义了一个名为fk_product_id的外键约束，将order_items表的product_id列与products表的product_id列关联起来，保证数据引用的完整性。
     • CONSTRAINT ck_quantity CHECK (quantity > 0)：定义了一个名为ck_quantity的检查约束，确保quantity列的值大于0。

3. 在SQL Server中：

   -- 创建商品表
   CREATE TABLE products (
       product_id INT PRIMARY KEY,
       product_name VARCHAR(255)
       -- 其他商品相关字段
       --...
   );
   
   -- 创建订单表
   CREATE TABLE orders (
       order_id INT PRIMARY KEY
       -- 其他订单相关字段
       --...
   );
   
   -- 创建订单详情表，并添加外键约束和检查约束
   CREATE TABLE order_items (
       order_item_id INT PRIMARY KEY,
       order_id INT,
       product_id INT,
       quantity INT,
       CONSTRAINT FK_product_id FOREIGN KEY (product_id) REFERENCES products(product_id),
       CONSTRAINT CK_quantity CHECK (quantity > 0),
       CONSTRAINT FK_order_id FOREIGN KEY (order_id) REFERENCES orders(order_id)
   );
   

   • 解释：
     • CONSTRAINT FK_product_id FOREIGN KEY (product_id) REFERENCES products(product_id)：在order_items表的product_id列上创建了一个名为FK_product_id的外键约束，确保product_id的引用完整性。
     • CONSTRAINT CK_quantity CHECK (quantity > 0)：创建了一个名为CK_quantity的检查约束，保证quantity列的值为正数。