1

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?

User1234567
  • 13
  • 3
  • While it might be possible in an entitity-relationship-diagram to have 1…N cardinality, there is no direct equivalent in an SQL database schema. One could achieve a lot with triggers, but this is usually not done. – Bergi Aug 19 '20 at 20:25
  • 1
    In theory, this is what the `ASSERTION` is for in the SQL standard. But in reality, no database supports them though. –  Aug 19 '20 at 21:02

1 Answers1

1

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

Bjarni Ragnarsson
  • 1,731
  • 1
  • 6
  • 8
  • 2
    Things like these are possible with deferred constraints that are only checked when the transaction is committed, but yeah the chicken-egg-problem makes this quite complicated. – Bergi Aug 19 '20 at 20:57