实现“两者,或者,但不是空”数据库中的要求 [英] Implementing a "Both, Either-or, but Not Null" Requirement in a Database
问题描述
我有一个网络应用程序的要求,指出用户应该可以上传指令文档(.pdf,.doc,.txt)或提供说明文本。用户可以上传文档并提供文本,或者可以进行一对一的操作,但是它们必须执行某些操作(不可为空)。这在数据库中如何设计?这会被认为是一个完整的子类型(见下文)?
I have requirement for a web app that states that a user should be able to either upload an instruction document(s) (.pdf, .doc, .txt) or provide text for the instructions. The user can upload a document and provide text, or they can do one-or-the-other, but they have to do something (not nullable). How would this be designed in a database? Would this be considered a complete sub-type (see below)?
这是一个更大的模式的小部分,所以我刚刚发布了我觉得这个特定问题是必要的。
This is tiny part of a larger schema, so I just posted what I felt was necessary for this particular question.
推荐答案
Ypercube的回答是好的,除了这可以实际上完全通过声明完整性完成,同时保持单独的表。诀窍是将推迟的循环外部键与一点创造性的非规范化相结合:
Ypercube's answer is fine, except this can, in fact, be done purely through declarative integrity while keeping separate tables. The trick is to combine deferred circular FOREIGN KEYs with a little bit of creative denormalization:
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;
插入文本是这样完成的:
Inserting Text is done like this:
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;
但是,尝试单独插入指令失败:
However, trying to insert Instruction alone fails on commit:
INSERT INTO Instruction (InstructionId, TextId) VALUES (4, 4);
COMMIT; -- Error (FOREIGN KEY violation).
尝试插入不匹配类型时,失败 / p>
Attempting to insert the "mismatched type" also fails on commit:
INSERT INTO Document (InstructionId) VALUES (1);
COMMIT; -- Error (FOREIGN KEY violation).
当然,尝试将错误值插入到指令失败提交前的时间):
And of course, trying to insert bad values into Instruction fails (this time before commit):
INSERT INTO Instruction (InstructionId, TextId) VALUES (5, 6); -- Error (CHECK violation).
INSERT INTO Instruction (InstructionId) VALUES (7); -- Error (CHECK violation).
这篇关于实现“两者,或者,但不是空”数据库中的要求的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!