Take the 2-minute tour ×
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.

Suppose I have a table containing job roles:

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

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

CREATE TABLE users
(
  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 at 22:50

1 Answer 1

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

Your Answer

 
discard

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.