Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Constraint on join table

Tags:

sql

postgresql

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.

like image 913
Ben Smith Avatar asked Mar 17 '23 16:03

Ben Smith


2 Answers

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.

like image 161
Gordon Linoff Avatar answered Mar 22 '23 23:03

Gordon Linoff


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)
);
like image 27
Mike Sherrill 'Cat Recall' Avatar answered Mar 22 '23 23:03

Mike Sherrill 'Cat Recall'