SQL Constraint on join table

Question

I have four tables in a PostgreSQL database:

• Company
• User (with a foreign key column company_id)
• Location (with a foreign key column company_id)
• UserLocations (an association table, with foreign key columns user_id and location_id)

Essentially:

• a company has many users and locations
• a user belongs to a company and has many locations
• a location belongs to a company and has many users

I want to know if there is a way for the database to constrain entries in the UserLocations association table such that the referenced user and location must have an identical company_id value. I don't want a user from company A to have a location from company B.

I can check this at my application layer (rails) but would be interested in making this a hard database level constraint if the option exists.

Answer 1

One way you can accomplish this is with foreign key references and redundancy.

So, the UserLocations table would have a UserId, LocationId, and CompanyId. It would then have the following foreign key relationships:

foreign key (UserId, CompanyId) references Users(UserId, CompanyId)
foreign key (LocationId, CompanyId) references Locations(LocationId, CompanyId)

Of course, you have to declare Users(UserId, CompanyId) and Locations(LocationId, CompanyId) as unique keys for the reference. This is a bit redundant, but it does guarantee the matching to company without creating triggers.

Answer 2

Overlapping foreign key constraints are your friend.

create table company (
  company_id integer primary key
);

-- Reserved words include "user". Better to use identifiers that
-- are not reserved words.
create table "user" (
  user_id integer primary key,
  company_id integer not null references company (company_id),
  -- Unique constraint lets this be the target of a foreign key reference.
  unique (user_id, company_id)
);

create table location (
  location_id integer primary key,
  company_id integer not null references company (company_id),
  unique (location_id, company_id)
);

create table user_location (
  user_id integer not null,
  location_id integer not null,
  company_id integer not null,
  -- Not sure what your primary key is here. 

  -- These foreign keys overlap on the column "company_id", so there
  -- can be only one value for it.
  foreign key (user_id, company_id) references "user" (user_id, company_id),
  foreign key (location_id, company_id) references location (location_id, company_id)
);