CODEWITHSUNDEEP
postgresqldatabase-designerdcardinality
User1234567
User1234567

Reputation: 13

Not null on both sides of One-to-many in PostgreSQL

How are one-to-many relationships implemented? It is often recommended to do something like:

CREATE TABLE instructor (
  instructor_id integer primary key,
  ...
);

CREATE TABLE course (
  course_id integer primary key,
  instructor_id integer not null references instructor(instructor_id),
  ...
);

While this guarantees that the course will have a instructor, it doesn't guarantee the instructor will have a course. I see drawings with business rules as in this page where it is supposedly possible: http://www2.cs.uregina.ca/~bernatja/crowsfoot.html

I am trying to make an implementation of b9 on that page, where instructors must have 1 or more courses and the course must be taught by one instructor. I've tried adding a not null foreign key to the instructor table along with deferred foreign keys to get around insert issues but the instructor_id in the course table is not a unique index, so that doesn't work. How can I guarantee that my instructor has a course?

Upvotes: 1

Views: 263

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

I would say this is not possible as you will need at some point to insert a new instructor or a course. If it's a new instructor, no course can reference it beforehand and thus you'll have an instructor without a course - and vice versa.

Best regards, Bjarni

Upvotes: 1

Related Questions