Ograniczenie klucza obcego do elementu tablicy?

Załóżmy, że mam tabelę zawierającą role zadań:

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

Załóżmy, że dalej mam tabelę, użytkowników, a każdy wiersz (określony użytkownik) może mieć dowolną liczbę ról zadań:

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

Prawdopodobnie powinienem upewnić się, że każdy członek users.roles[]istnieje w roles.role. Wydaje mi się, że to, czego chcę, to ograniczenie klucza obcego na każdym elemencie, users.roles[]takie, że jeśli odwołuje się roles.role.

Nie wydaje się to możliwe w przypadku postgres. Czy patrzę na to w niewłaściwy sposób? Jaki jest sugerowany „właściwy” sposób na poradzenie sobie z tym?

Pracowano nad obsługą obcych kluczy macierzy w celu wprowadzenia ich do PostgreSQL 9.3, ale nie zrobiło to cięcia w wydaniu z powodu problemów z wydajnością i niezawodnością. Wydaje się, że nie jest opracowywany dla 9.4.

W tej chwili musisz trzymać się zwykłego podejścia relacyjnego polegającego na użyciu „tabeli łączenia” do modelowania relacji m: n.

CREATE TABLE user_roles (
   username character varying(12) references users(username),
   "role" character varying(80) references roles("role"),
   PRIMARY KEY(username, "role")
);

W tym przypadku sugeruję użycie kluczy zastępczych zamiast przechowywania nazw użytkowników / ról bezpośrednio w tabeli łączenia. Gdy po raz pierwszy chcesz zmienić nazwę użytkownika lub roli, będziesz zadowolony, że użyłeś kluczy zastępczych. Po prostu nałóż uniqueograniczenie na roles."role"i users.username.

Właśnie zrobiłem coś podobnego dla kolegi. Zasadniczo stworzyłem ukryty stół, który zawierał jeden wiersz dla każdej pary (użytkownika, roli) z odpowiednimi ograniczeniami. Tabela użytkowników była wówczas widokiem ukrytej tabeli ze wszystkimi rolami złożonymi w tablicę. Następnie umożliwiłem wstawienie do widoku, dodając odpowiednią regułę. Oto jak:

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=# 

Mam nadzieję że to pomogło. Możesz uczynić go nieco bardziej wydajnym i dodać więcej reguł w zależności od twoich wymagań.

Po otrzymaniu łatki, która pozwala na więcej tej funkcji tutaj

Po prostu użyj: ELEMENT REFERENCES relation( field )

Dla intencji:

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