SQL 没有唯一约束匹配引用表的给定键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8317006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
No unique constraint matching given keys for referenced table
提问by daydreamer
I have a date_dimensiontable definition:
我有一个date_dimension表定义：
CREATE TABLE date_dimension
(
id integer primary key,
date text,
year double precision,
year_for_week double precision,
quarter double precision
);
I am trying to create a facttable that fails
我正在尝试创建一个fact失败的表
create table fact (
id serial primary key,
contract integer,
component integer,
evaluation_date integer,
effective_date integer,
foreign key (evaluation_date, effective_date) references date_dimension(id, id)
);
The error is :
错误是：
ERROR: there is no unique constraint matching given keys for referenced
table "date_dimension"
SQL state: 42830
I am not sure how to fix this.
我不知道如何解决这个问题。
采纳答案by Mark Byers
I think you are looking for two separate foreign keys:
我认为您正在寻找两个单独的外键：
foreign key (evaluation_date) references date_dimension(id),
foreign key (effective_date) references date_dimension(id)
回答by Flimzy
The error tells you the problem: You don't have a unique constraint on date_dimensionthat matches your foreign key constraint.
该错误告诉您问题：您没有date_dimension与外键约束匹配的唯一约束。
However, this leads to the bigger design problem: Your foreign key relationship doesn't make any sense.
但是，这会导致更大的设计问题：您的外键关系没有任何意义。
You could possibly solve your "problem" with:
您可以通过以下方式解决您的“问题”：
CREATE UNIQUE INDEX date_dimension(id,id);
But that's dumb, because idis always the same. It could also be expressed as:
但这很愚蠢，因为id总是一样的。也可以表示为：
FOREIGN KEY (evaluation_date) REFERENCES date_dimension(id);
Then getting rid of the effective_datecolumn, which would always be identical to evaluation_datein your example.
然后摆脱该effective_date列，该列始终与evaluation_date您的示例相同。
Or... you probably reallywant two FK relationships:
或者……你可能真的想要两个 FK 关系：
FOREIGN KEY (evaluation_date) REFERENCES date_dimension(id);
FOREIGN KEY (effective_date) REFERENCES date_dimension(id);
回答by Marc Morin
Don't you just want to create two separate foreign key references to the date dimension as follows:
难道您只想为日期维度创建两个单独的外键引用，如下所示：
create table fact (
id serial primary key,
contract integer,
component integer,
evaluation_date integer,
effective_date integer,
foreign key (evaluation_date) references date_dimension(id),
foreign key (effective_date) references date_dimension(id)
);
