假设我有一个包含工作角色的表：

CREATE TABLE roles
(
  "role" character varying(80) NOT NULL,
  CONSTRAINT "role" PRIMARY KEY (role)
);


假设我还有一个表，用户，并且每一行（一个特定的用户）可以有任意数量的工作角色：

CREATE TABLE users
(
  username character varying(12) NOT NULL,
  roles character varying(80)[] NOT NULL,
  CONSTRAINT username PRIMARY KEY (username)
);


我可能应该确保users.roles[]的每个成员都存在于role.role中。在我看来，我想要的是对users.roles[]的每个成员的外键约束，以便如果引用role.role。

这在postgres中似乎是不可能的。我看错了方向吗？建议的“正确”处理方式是什么？

#1 楼

支持数组外键的目的是使它进入PostgreSQL 9.3，但是由于性能和可靠性问题，它没有降低发行版的数量。在9.4上似乎还没有使用它。

目前，您需要坚持使用“联接表”对m：n关系建模的常用关系方法。 br /> 
CREATE TABLE user_roles (
   username character varying(12) references users(username),
   "role" character varying(80) references roles("role"),
   PRIMARY KEY(username, "role")
);


在这种情况下，我也建议使用代理键，而不是将用户名/角色名称直接存储在联接表中。第一次要重命名用户或角色时，您会很高兴使用代理键。只需在unique和roles."role"上放置一个users.username约束即可。

#2 楼

我刚刚为同事做了类似的事情。本质上，我制作了一个隐藏表，其中每个对（用户，角色）对都包含一行，并具有适当的约束。然后，用户表是隐藏表的视图，其中所有角色都组合到一个数组中。然后，通过添加适当的规则，可以将其插入到视图中。方法如下：

trailer=# create table harvester (id int unique, label text);
CREATE TABLE
trailer=# insert into harvester values (1,'grain'), (2,'cricket');
INSERT 0 2
trailer=# create table donkey (id int, others int references
harvester(id));
CREATE TABLE
trailer=# create unique index donkey_ears on donkey (id, others);
CREATE INDEX
trailer=# create view combine as select id, array_agg(others) as others
from donkey group by id;
CREATE VIEW
trailer=# create rule combine_insert as on insert to combine do instead
(delete from donkey where donkey.id=new.id;insert into donkey select
new.id,unnest(new.others) );
CREATE RULE
trailer=# insert into combine values (1,'{1,2}');INSERT 0 2
trailer=# select * from combine ;
id | others 
----+--------
  1 | {1,2}
(1 row)

trailer=# insert into combine values (1,'{1,2}');
INSERT 0 2
trailer=# select * from combine ;
 id | others 
----+--------
  1 | {1,2}
    (1 row)

trailer=# insert into combine values (2,'{1,2,3}');
ERROR:  insert or update on table "donkey" violates foreign key
constraint "donkey_others_fkey"
DETAIL:  Key (others)=(3) is not present in table "harvester".
trailer=#


希望对您有所帮助。您可以提高效率，并根据需要添加更多规则。

#3 楼

一旦获得允许该功能更多的修补程序，请在这里使用

只需使用：ELEMENT REFERENCES relation( field )

例如：

CREATE TABLE drivers (
   driver_id integer PRIMARY KEY,
   first_name text,
   last_name text,
   ...
);



CREATE TABLE races (
   race_id integer PRIMARY KEY,
   title text,
   race_day DATE,
   ...
   practice1_positions integer[] ELEMENT REFERENCES drivers,
   practice2_positions integer[] ELEMENT REFERENCES drivers,
   practice3_positions integer[] ELEMENT REFERENCES drivers,
   qualifying_positions integer[] ELEMENT REFERENCES drivers,
   final_positions integer[] ELEMENT REFERENCES drivers
);


评论

看起来不错的主意，但是如果不手动修补引擎就无法使用-这就是投票不足的原因……

– langpavel
17年5月29日在23:03