CODEWITHSUNDEEP
sqlpostgresqldatabase-designone-to-many
user2675516
user2675516

Reputation:

How to enforce single direction uniqueness in postgres?

Suppose I have a job_assignment table that contains worker_id and job_id from the worker and job tables.

A worker can have many jobs but a job can be assigned to only one worker. Is there a way to ensure this in postgres?

If I put UNIQUE(worker_id, job_id), this will only be a one to one mapping. I have read up on UNIQUE FOREIGN KEY but I am still not sure if it is the right way to do this.

Upvotes: 1

Views: 189

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246298

This should not be modeled with an intermediate job_assignment table — that would be the correct way to model a many-to-many relationship.

Instead you should add a foreign key column worker_id to job. That way it is guaranteed that a job can only belong to one worker.

If there are fields you want to store in a job_assignment, you could instead add a job_assignment_id foreign key to job and a worker_id foreign key to job_assignment. But I'd say that you might as well add these data to job. It's fine to create a data model that reflects reality, but your model should also allow your SQL queries to be simple, with no more joins than necessary.

Upvotes: 1

melpomene
melpomene

Reputation: 85767

I wouldn't use an intermediate table for this, but you can do what you asked for by adding a UNIQUE(job_id) constraint to job_assignment.

This ensures every job is listed at most once in job_assignment, thus assigned to at most one worker.

Upvotes: 0

Related Questions