错误:没有唯一约束匹配给定键的引用表“bar"； [英] ERROR: there is no unique constraint matching given keys for referenced table "bar"

问题描述

尝试在 Postgres 9.1 中创建此示例表结构:

CREATE TABLE foo (名称 VARCHAR(256) 主键);创建表格栏(pkey 串行主键，foo_fk VARCHAR(256) NOT NULL REFERENCES foo(name),名称 VARCHAR(256) 非空，唯一(foo_fk，名称));创建表 baz(pkey 串行主键，bar_fk VARCHAR(256) NOT NULL REFERENCES bar(name),名称 VARCHAR(256));

运行上面的代码会产生一个错误，这对我来说没有意义:

<块引用>

注意:CREATE TABLE/PRIMARY KEY 将创建隐式索引foo_pkey"；对于表foo"注意:CREATE TABLE 将创建隐式序列bar_pkey_seq"；用于串行列bar.pkey"注意:CREATE TABLE/PRIMARY KEY 将创建隐式索引bar_pkey"；用于表酒吧"注意:CREATE TABLE/UNIQUE 将创建隐式索引bar_foo_fk_name_key"；用于表酒吧"注意:CREATE TABLE 将创建隐式序列baz_pkey_seq"；用于串行列baz.pkey"注意:CREATE TABLE/PRIMARY KEY 将创建隐式索引baz_pkey"；表baz"错误:没有唯一约束匹配给定键的引用表bar"；

********** 错误 **********错误:没有唯一约束匹配给定键的引用表bar"；SQL 状态:42830

谁能解释为什么会出现这个错误?

解决方案

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

假设您在 bar 表上有 2 行包含名称 'ams' 并且您在 baz 上插入一行 <bar_fk 上的 code>'ams'，由于有两行匹配，它指的是 bar 上的哪一行?

Trying to create this example table structure in Postgres 9.1:

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)
);

Running the above code produces an error, which does not make sense to me:

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

Can anyone explain why this error arises?

解决方案

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

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?

这篇关于错误:没有唯一约束匹配给定键的引用表“bar"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！