Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have a table containing job roles:

  "role" character varying(80) NOT NULL,

Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number of job roles:

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

I should probably make sure that each member of users.roles[] exists in roles.role. It seems to me that what I want is a foreign key constraint on each member of users.roles[] such that if references roles.role.

This doesn't seem possible with postgres. Am I looking at this the wrong way? What is the suggested "right" way to handle this?

share|improve this question
I think the proper way of handling this is not using arrays for attributes. Since the relationship between users and roles seems to be "many-to-many", it is better implemented using a "link", or "junction" table on which you can define a proper RI constraint. – mustaccio Mar 4 '14 at 22:50

Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4.

At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship.

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

I suggest using surrogate keys in this case, too, rather than storing the usernames/role names directly in the join table. The first time you want to rename a user or role you'll be happy you used surrogate keys. Just place a unique constraint on roles."role" and users.username.

share|improve this answer
The first time you want to rename a user or role you'll be happy you used surrogate keys. No, keep your username and role columns, and don't add meaningless columns. Just add ON UPDATE CASCADE. – Jesse Dhillon Oct 17 '15 at 4:32
@JesseDhillon FK cascades get "interesting" in big DBs under load. I don't love surrogate keys but they do have their place. I also tend to bow to the growing number of tools that can't be bothered properly supporting anything else :( . What really annoys me is seeing a surrogate key on a n:m join table. Wtf? – Craig Ringer Oct 17 '15 at 5:33
I would agree that frequently changing columns are poor choices for PKs, but on the whole I tend towards natural keys myself. Agreed, re tools enforcing arbitrary limitations, which is why I love SQLAlchemy. The book "SQL Antipatterns" is basically a documentation of Rails ActiveRecord design decisions. – Jesse Dhillon Oct 18 '15 at 7:25
@JesseDhillon Yeah. ActiveRecord. It's so unfortunate that many people are learning ActiveRecord as the norm for "databases". Sigh. Not that Hibernate is much better. – Craig Ringer Oct 18 '15 at 7:33

I just made something similar for a colleague. Essentially I made a hidden table that contained one row for each (user,role) pair with suitable constraints. The user table was then a view of the hidden table with all the roles assembled into an array. I then made it possible to insert into the view by adding an appropriate rule. Here is how:

trailer=# create table harvester (id int unique, label text);
trailer=# insert into harvester values (1,'grain'), (2,'cricket');
trailer=# create table donkey (id int, others int references
trailer=# create unique index donkey_ears on donkey (id, others);
trailer=# create view combine as select id, array_agg(others) as others
from donkey group by id;
trailer=# create rule combine_insert as on insert to combine do instead
(delete from donkey where;insert into donkey select,unnest(new.others) );
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}');
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".

I hope that helps. You can make it a bit more efficient and add more rules depending on your requirements.

share|improve this answer

Once you get the patch that allows that functionality more here

Just use: ELEMENT REFERENCES relation( field )

For intance:

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

   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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.