如何确保无关表之间的完整性? [英] How do I ensure integrity between unrelated tables?

问题描述

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

I just recently started learning database design, and I'm am working with Oracle 11G and SQL Developer.

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

I have these 3 business rules for a DB:

• 每个OFFICER都必须注册一个且只有一个保险公司.每个保险公司可以注册一名或多名官员
• 每个保险公司必须提供至少五种不同类型的保险类型.每种保险类型最多可以由4个保险公司提供，或者根本不提供
• 每个保险类型可由一名或多名官员订购.每个官员最多可以订阅同一公司提供的五种不同的保险盖.

. .

到目前为止，还算不错，我想出了五个表( INS_COY，OFFR，INS_TYPE，PROVIDE和SUBSCRIBE ).由于INS_COY和INS_TYPE之间的关系以及OFFR和INS_TYPE之间的关系都是M:M关系，所以PROVIDE和SUBSCRIBE是组合表.

so far, so good, i came up with five TABLES (INS_COY, OFFR, INS_TYPE, PROVIDE, and SUBSCRIBE). PROVIDE and SUBSCRIBE came about as composite tables since the relationships between INS_COY and INS_TYPE, and OFFR and INS_TYPE are both M:M relationships.

PK和FK属性如下:

PK and FK attributes for each of the table is as below:

INS_COY TABLE
coy_id -PK

INS_COY TABLE
coy_id - PK

优惠表
offr_id -PK
coy_id -(FK引用INS_COY.coy_id))

OFFR TABLE
offr_id - PK
coy_id - (FK referencing INS_COY.coy_id))

INS_TYPE表
type_id -PK

INS_TYPE TABLE
type_id - PK

提供
coy_id 和 type_id -(复合PK)
coy_id -(FK引用COY.coy_id)
type_id -(FK引用ins_type.type_id)

PROVIDE
coy_id and type_id - (composite PK)
coy_id - (FK referencing COY.coy_id)
type_id - (FK referencing ins_type.type_id)

订阅
naf_no 和 type_id -(复合PK)
naf_no -(FK引用offr.offr_id)
type_id (FK引用ins_type.type_id)

SUBSCRIBE
naf_no and type_id - (composite PK)
naf_no - (FK referencing offr.offr_id)
type_id (FK referencing ins_type.type_id)

.
.
.

.
.
.

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

the tables have been sucessfully created, and sample data inserted.

因此，问题是-在订阅表上，我如何确保附着在OFFR_ID上的TYPE_ID是被他所迷住的亲人提供的INS_TYPE?

示例数据表

ie ...从表中，"offy 4250"被注册到"coy 1"中，而"coy 1"不提供"ins_type 13"，但是，因为没有限制来检查此内容，因此"offer 1" "订阅了SUBSCRIBE TABLE上的"ins_type 13".

i.e ...from the tables, "offr 4250" is enrolled in "coy 1", and "coy 1" doesn't provide "ins_type 13", however, because there's no constraint to check this, "offr 1" is subscribed to "ins_type 13" on the SUBSCRIBE TABLE.

推荐答案

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

You can do it using controlled redundancy and composite FK constraints:

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约束.

I added a composite unique key (offr_id, coy_id) to support a composite FK constraint on the subscribe table.

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约束.

The composite primary key here is perfect for a composite FK constraint on the subscribe table.

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约束没有更新异常的风险

Overlapping composite FK constraints will ensure that an officer can only subscribe to insurance offered by the company he/she is enrolled in. coy_id is logically redundant but required for integrity and there's no risk of update anomalies due to the FK constraints.

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

Alternatively, you could use triggers to check that the values are related via inner joins:

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，但希望它将为您指明正确的方向.

Disclaimer: I was unable to test this on SqlFiddle and don't have Oracle installed, but hopefully it'll point you in the right direction.

这篇关于如何确保无关表之间的完整性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！