oracle - 在数据库中实现 "Both, Either-or, but Not Null"要求

Question

我要求一个网络应用程序声明用户应该能够上传说明文档(.pdf、.doc、.txt)或提供说明文本。用户可以上传文档并提供文本，或者他们可以做一个或另一个，但他们必须做某事(不可为空)。这将如何在数据库中设计？这会被视为一个完整的子类型(见下文)吗？

这是更大模式的一小部分，所以我只是发布了我认为对这个特定问题有必要的内容。

Answer 1

Ypercube's answer 很好，除了这实际上可以完全通过声明完整性来完成，同时保持单独的表。诀窍是将延迟循环外键与一点创造性的反规范化相结合:

CREATE TABLE Instruction (
    InstructionId INT PRIMARY KEY,
    TextId INT UNIQUE,
    DocumentId INT UNIQUE,
    CHECK (
        (TextId IS NOT NULL AND InstructionId = TextId)
        OR (DocumentId IS NOT NULL AND InstructionId = DocumentId)
    )
);

CREATE TABLE Text (
    InstructionId INT PRIMARY KEY,
    FOREIGN KEY (InstructionId) REFERENCES Instruction (TextId) ON DELETE CASCADE
);

CREATE TABLE Document (
    InstructionId INT PRIMARY KEY,
    FOREIGN KEY (InstructionId) REFERENCES Instruction (DocumentId) ON DELETE CASCADE
);

ALTER TABLE Instruction ADD FOREIGN KEY (TextId) REFERENCES Text DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE Instruction ADD FOREIGN KEY (DocumentId) REFERENCES Document DEFERRABLE INITIALLY DEFERRED;

插入文本是这样完成的:

INSERT INTO Instruction (InstructionId, TextId) VALUES (1, 1);
INSERT INTO Text (InstructionId) VALUES (1);
COMMIT;

像这样插入文档:

INSERT INTO Instruction (InstructionId, DocumentId) VALUES (2, 2);
INSERT INTO Document (InstructionId) VALUES (2);
COMMIT;

然后像这样插入文本和文档:

INSERT INTO Instruction (InstructionId, TextId, DocumentId) VALUES (3, 3, 3);
INSERT INTO Text (InstructionId) VALUES (3);
INSERT INTO Document (InstructionId) VALUES (3);
COMMIT;

但是，在提交时尝试单独插入指令失败:

INSERT INTO Instruction (InstructionId, TextId) VALUES (4, 4);
COMMIT; -- Error (FOREIGN KEY violation).

尝试插入“不匹配的类型”在提交时也失败:

INSERT INTO Document (InstructionId) VALUES (1);
COMMIT; -- Error (FOREIGN KEY violation).

当然，尝试向指令中插入错误值失败(这次是在提交之前):

INSERT INTO Instruction (InstructionId, TextId) VALUES (5, 6); -- Error (CHECK violation).
INSERT INTO Instruction (InstructionId) VALUES (7); -- Error (CHECK violation).