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?