如何确保无关表之间的完整性？

小编典典

如何确保无关表之间的完整性？

sql

我刚刚开始学习数据库设计，并且我正在与Oracle 11G和SQL Developer一起工作。

我对数据库有以下3条业务规则：

每个官员必须注册一个并且只能注册一个保险公司。每个保险公司可以注册一名或多名官员
每个保险公司必须提供至少五种不同类型的保险类型。每种保险类型最多可以由4个保险公司提供，或者根本不提供
每个保险类型可以由一个或多个官员来订阅。每个官员最多可以订阅同一家公司提供的五种不同的保险盖。

。。。

到目前为止， 一切顺利 ，我提出了五个表（ INS_COY，OFFR，INS_TYPE，PROVIDE和SUBSCRIBE
）。由于INS_COY和INS_TYPE之间的关系以及OFFR和INS_TYPE之间的关系都是M：M关系，所以PROVIDE和SUBSCRIBE是组合表。

每个表的PK和FK属性如下：

INS_COY TABLE
coy_id -PK

OFFR TABLE
offr_id -PK
coy_id-（FK引用INS_COY.coy_id）

INS_TYPE TABLE
type_id -PK

PROVIDE
coy_id和TYPE_ID - （复合PK）
coy_id - （FK引用COY.coy_id）
TYPE_ID - （FK引用ins_type.type_id）

SUBSCRIBE
naf_no和TYPE_ID - （复合PK）
naf_no - （FK引用offr.offr_id）
TYPE_ID（FK引用ins_type.type_id）

。
。
。

表已成功创建，并插入了示例数据。

因此，问题是- 在订阅表上，我如何确保附在OFFR_ID上的TYPE_ID是由他所迷恋的COY提供的INS_TYPE？

样本数据表

即…从表中，“ offer 4250”被注册到“ coy 1”中，而“ coy 1”不提供“ ins_type
13”，但是，由于没有限制来检查，因此订阅了“ offr 1”订阅表上的“ ins_type 13”。

阅读 173

2021-05-05

共1个答案

小编典典

您可以使用受控冗余和复合FK约束来做到这一点：

CREATE TABLE offr (
    offr_id INT NOT NULL,
    coy_id INT NOT NULL,
    PRIMARY KEY (offr_id),
    FOREIGN KEY (coy_id) REFERENCES ins_coy (coy_id),
    UNIQUE KEY (offr_id, coy_id)
);

我添加了一个复合唯一键（offr_id，coy_id）来支持subscribe表上的复合FK约束。

CREATE TABLE provide (
    coy_id INT NOT NULL,
    type_id INT NOT NULL,
    PRIMARY KEY (coy_id, type_id),
    FOREIGN KEY (coy_id) REFERENCES ins_coy (coy_id)
);

这里的复合主键非常适合subscribe表上的复合FK约束。

CREATE TABLE subscribe (
    naf_no INT NOT NULL,
    coy_id INT NOT NULL,
    type_id INT NOT NULL,
    PRIMARY KEY (naf_no, type_id),
    FOREIGN KEY (naf_no, coy_id) REFERENCES offr (offr_id, coy_id),
    FOREIGN KEY (coy_id, type_id) REFERENCES provide (coy_id, type_id)
);

重叠的复合FK约束将确保官员只能订阅他/她注册的公司提供的保险。coy_id从逻辑上讲是冗余的，但对于完整性而言是必需的，并且不会因FK约束而存在更新异常的风险。

或者，您可以使用触发器来检查值是否通过内部联接相关：

CREATE TRIGGER check_subscribe BEFORE INSERT OR UPDATE ON subscribe
FOR EACH ROW
WHEN NOT EXISTS (
    SELECT 1
    FROM offr
    INNER JOIN provide ON offr.coy_id = provide.coy_id
    WHERE offr.offr_id = new.naf_no AND provide.type_id = new.type_id
)
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Officers can only subscribe to types provided by their company');

免责声明：我无法在SqlFiddle上进行测试，也没有安装Oracle，但希望它将为您指明正确的方向。

2021-05-05