多个表的唯一约束 [英] Unique constraint over multiple tables

问题描述

假设我们有这些表：

CREATE TABLE A (
    id SERIAL NOT NULL PRIMARY KEY
);
CREATE TABLE B (
    id SERIAL NOT NULL PRIMARY KEY
);
CREATE TABLE Parent (
    id SERIAL NOT NULL PRIMARY KEY,
    aId INTEGER NOT NULL REFERENCES A (id),
    bId INTEGER NOT NULL REFERENCES B (id),
    UNIQUE(aId, bId)
);
CREATE TABLE Child (
    parentId INTEGER NOT NULL REFERENCES Parent (id),
    createdOn TIMESTAMP NOT NULL
);

是否可以对 Child ，这样对于 Child 中的所有行，最多只能引用一个 Parent code> aId ？陈述另一种方式，我可以创建一个唯一约束，以便上述表的联接不会有重复的 aId 吗？我没有考虑-我可以找到的每个数据库的语法似乎都与每个约束约束在一个表上-但这可能是我缺乏想象力的原因。 （当然，对 Child 上的 aId 进行非规范化是一种解决方案。）

Is it possible to create a unique constraint on Child such that for all rows in Child at most one references a Parent having some value of aId? Stated another way can I created a unique constraint so that the join of the above tables will have no duplicate aId? I'm thinking not--the grammars of every database I could find seem tied to one table per constraint--but that might be a lack of imagination on my part. (De-normalizing to include aId on Child is one solution, of course.)

推荐答案

您可以尝试以下方法。您必须在Parent中的（id，aId）上创建一个冗余的UNIQUE约束（SQL很愚蠢，不是吗？！）。

You could try the following. You have to create a redundant UNIQUE constraint on (id, aId) in Parent (SQL is pretty dumb isn't it?!).

CREATE TABLE Child
(parentId INTEGER NOT NULL,
 aId INTEGER NOT NULL UNIQUE,
FOREIGN KEY (parentId,aId) REFERENCES Parent (id,aId),
createdOn TIMESTAMP NOT NULL);

可能更好的解决方案是从Child表中完全删除parentId，然后添加 bId 而是仅基于（aId，bId）引用父表：

Possibly a much better solution would be to drop parentId from the Child table altogether, add bId instead and just reference the Parent table based on (aId, bId):

CREATE TABLE Child
(aId INTEGER NOT NULL UNIQUE,
 bId INTEGER NOT NULL,
FOREIGN KEY (aId,bId) REFERENCES Parent (aId,bId),
createdOn TIMESTAMP NOT NULL);

有什么理由不能这样做？

Is there any reason why you can't do that?

这篇关于多个表的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！