SQL 主外键关系与多对多关系

SQL 主外键关系与多对多关系学习笔记

在数据库设计中，主外键关系和多对多关系是常见的表关系类型。

1. 主外键关系

主外键关系用于建立两个表之间的关联。主键表（父表）中的主键列被外键表（子表）引用，以确保数据的完整性和一致性。

（1）创建父表（用户表）

-- 创建用户表（父表）
CREATE TABLE T_USER (
    ID VARCHAR2(5),
    NAME VARCHAR2(10) NOT NULL,
    CONSTRAINT T_USER_ID_PK PRIMARY KEY (ID) -- 主键约束
);

-- 插入父表数据
INSERT INTO T_USER VALUES (1, '小明');
INSERT INTO T_USER VALUES (2, '小红');
COMMIT;

-- 查询父表
SELECT * FROM T_USER;

（2）创建子表（商品表）

-- 创建商品表（子表）
CREATE TABLE T_PRODUCT (
    ID VARCHAR2(10),
    NAME VARCHAR2(10),
    PRICE FLOAT,
    U_ID VARCHAR2(5),
    CONSTRAINT T_PRODUCT_ID_PK PRIMARY KEY (ID), -- 主键约束
    CONSTRAINT T_PRODUCT_U_ID_FK FOREIGN KEY (U_ID) -- 外键约束
        REFERENCES T_USER (ID) -- 关联父表的主键
        ON DELETE CASCADE -- 级联删除
);

-- 添加检查约束
ALTER TABLE T_PRODUCT ADD CONSTRAINT T_PRODUCT_PRICE_CK CHECK (PRICE BETWEEN 1 AND 100);

-- 插入子表数据
INSERT INTO T_PRODUCT VALUES ('1001', '火腿肠', 1.00, 1);
INSERT INTO T_PRODUCT VALUES ('1002', '方便面', 3.50, 1);
INSERT INTO T_PRODUCT VALUES ('1003', '面包', 4.00, 2);
COMMIT;

-- 查询子表
SELECT * FROM T_PRODUCT;

（3）表连接查询

-- 查询用户和商品的关联数据
SELECT P.*, U.NAME
FROM T_USER U
LEFT JOIN T_PRODUCT P ON U.ID = P.U_ID;

2. 多对多关系

多对多关系需要通过第三方关联表来实现。例如，一个用户可以购买多个商品，一个商品也可以被多个用户购买。

（1）创建关联表

-- 创建关联表
CREATE TABLE T_P_U (
    ID VARCHAR2(10) PRIMARY KEY,
    PID VARCHAR2(10),
    U_ID VARCHAR2(5),
    CONSTRAINT T_P_U_PID_FK FOREIGN KEY (PID) -- 商品表外键
        REFERENCES T_PRODUCT (ID),
    CONSTRAINT T_P_U_U_ID_FK FOREIGN KEY (U_ID) -- 用户表外键
        REFERENCES T_USER (ID)
);

-- 插入关联表数据
INSERT INTO T_P_U VALUES (100, '1001', 1);
INSERT INTO T_P_U VALUES (101, '1002', 1);
INSERT INTO T_P_U VALUES (102, '1003', 2);
INSERT INTO T_P_U VALUES (103, '1001', 2);
COMMIT;

-- 查询关联表
SELECT T.ID, P.NAME, P.PRICE, U.NAME
FROM T_P_U T, T_PRODUCT P, T_USER U
WHERE T.PID = P.ID AND T.U_ID = U.ID;

3. 级联删除

级联删除是指删除父表中的记录时，自动删除子表中关联的记录。

示例：

-- 删除父表记录（级联删除子表记录）
DELETE FROM T_USER WHERE ID = 1;

-- 查询子表
SELECT * FROM T_PRODUCT;

4. 删除表

（1）删除子表

-- 删除子表
DROP TABLE T_PRODUCT;

（2）删除父表（级联删除约束）

-- 删除父表（级联删除约束）
DROP TABLE T_USER CASCADE CONSTRAINTS;