是什么导致错误:没有唯一的约束条件匹配给定表的键? [英] What is causing ERROR: there is no unique constraint matching given keys for referenced table?

问题描述

在下面的示例表结构中给出了一个错误:没有给引用表的键匹配的唯一约束，并且盯着它看了片刻，现在我不知道为什么在这种情况下会出现此错误.

Below example table structure gives an ERROR: there is no unique constraint matching given keys for referenced table, and having stared at it for while now I can't figure out why this error arises in this situation.

BEGIN;

CREATE TABLE foo (
    name                VARCHAR(256) PRIMARY KEY
);

CREATE TABLE bar(
    pkey        SERIAL PRIMARY KEY,
    foo_fk      VARCHAR(256) NOT NULL REFERENCES foo(name), 
    name        VARCHAR(256) NOT NULL, 
    UNIQUE (foo_fk,name)
);

CREATE TABLE baz(   
    pkey            SERIAL PRIMARY KEY,
    bar_fk          VARCHAR(256) NOT NULL REFERENCES bar(name),
    name            VARCHAR(256)
);

COMMIT;

运行上面的代码会给出以下错误，对我来说这没有意义，任何人都可以解释为什么会出现此错误.我正在使用postgres 9.1

Running the above code gives the following error, which does not make sense to me, can anyone explain why this error arises. I am using postgres 9.1

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
NOTICE:  CREATE TABLE will create implicit sequence "bar_pkey_seq" for serial column "bar.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "bar_foo_fk_name_key" for table "bar"
NOTICE:  CREATE TABLE will create implicit sequence "baz_pkey_seq" for serial column "baz.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "baz_pkey" for table "baz"
ERROR:  there is no unique constraint matching given keys for referenced table "bar"


********** Error **********

ERROR: there is no unique constraint matching given keys for referenced table "bar"
SQL state: 42830

推荐答案

这是因为bar表上的name列没有 UNIQUE 约束.

It's because the name column on the bar table does not have the UNIQUE constraint.

因此，假设您在bar表上有2行包含名称'ams'，并在baz上插入一行，而在bar_fk上插入'ams'，则该行将引用bar因为有两行匹配?

So imagine you have 2 rows on the bar table that contain the name 'ams' and you insert a row on baz with 'ams' on bar_fk, which row on bar would it be referring since there are two rows matching?

这篇关于是什么导致错误:没有唯一的约束条件匹配给定表的键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！