Enforce 1 or many relationship

Question

In the er diagram below (not mine),

A Class can have many instructors via a standard junction table. Is there a way of designing the Instructors/Classes tables using constraints to ensure that a Class must have at least one Instructor? I.e. 1 or many instead of 0 or many? So given Classes.class_id=1, there must be an Instructors_Classes(class_id=1, instructor_id=?) Or is this something that you'd just use triggers to enforce?

As a secondary, can anyone suggest a good way of identifying one, and only one Instructor as the primary instructor for a class? Currently I've added a field Instructors_Classes.IsPrimary (number) and applied a unique constraint on (class_id, IsPrimary) and a validation constraint of IsPrimary=Null OR 1, so only 1 instructor can have the value 1 per Class. Then when I want to get a list of all Classes, I can just join on Instructors_Classes.class_id WHERE IsPrimary=1 so that I get a single value for the Instructor of that class. This just feels a bit wrong but I'm not a db person so I don't know if there's a more conventional way of doing it. I thought of adding a Classes.instructor_id field but couldn't work out how to enforce the link between it and the Instructors_Classes table.

Serg · Accepted Answer · 2016-06-21T12:14:52.870

Really "a Class must have at least one Instructor" constraint could be checked only at transaction commit, because first we need insert a Classes and only after at least one Classes exists we can refer it in Instructors_Classes. No, currently most DBMSes do not implement features like that. Constraints are checked per statements, this check is not defered till transaction commit. Postrgesql can defer FK and some others constraints untill commit (thanks, ypercubeᵀᴹ).
You may implement a trigger on delete from Instructors_Classes which will prevent deletion of the last instructor of the class.

But your second "primary instructor for a class" requirement helps a bit. Add Classes.PrimaryInstructor referencing Instructors_Classes nullable. After Classes.PrimaryInstructor value is set this FK will ensure (with proper ON DELETE/UPDATE action) at least primary instructor for a class persists.
EDIT
To be precise above FK should be composite including also Classes PK ,

create table Classes (
...
constraint FK_prim_instr foreign key (class_id, primary_instructor) references class_instructors(class_id,instructor_id)
...
);

EDIT 2
Sample script

-- MS Sql Server 2014 

create table classes ( 
    class_id int not null primary key
    , primary_instructor int null
);

create table class_instructors(
    class_id int not null
    , instructor_id int not null 
    , constraint PK_cl_instr 
      primary key (class_id, instructor_id)
    , constraint FK_class_instr 
      foreign key (class_id)
      references classes (class_id)
);
go
alter table classes 
add constraint FK_Prim_instr 
      foreign key (class_id, primary_instructor)
      references class_instructors(class_id, instructor_id)

insert classes (class_id) 
values (1);

insert class_instructors (class_id,instructor_id) 
values (1, 10),(1,20);

update classes 
set primary_instructor = 20
where class_id = 1;

select * from classes;
select * from class_instructors ;

-- this will fail
insert class_instructors (class_id,instructor_id) 
values (2,10);

-- this will fail too
update classes 
set primary_instructor = 30
where class_id = 1;

Lennart - Slava Ukraini · Answer 2 · 2016-06-21T10:16:40.297

If one instructor is mandatory you could make that an mandatory attribute of classes:

create table classes
( class_id ... not null primary key
, ...
, primary_instructor ... not null
, constraint ... foreign key (primary_instructor)
      references instructors (instructor_id)
          on delete ...
          on update ...
);

A reasonable requirement is that a primary instructor is not allowed as an additional instructor on the same class. SQL allows us to declare CHECK constraints with sub-selects, so that would be a natural choice. Unfortunately not many vendors implement this, so a workaround is:

create table classes
( class_id ... not null primary key
, ...
, primary_instructor ... not null

, constraint ... unique (class_id, primary_instructor)

, constraint ... foreign key (primary_instructor)
      references instructors (instructor_id)
          on delete ...
          on update ...
);


create table additional_class_instructors
( class_id ... not null
, primary_instructor ... not null 
, constraint ... 
      primary key (class_id, instructor_id)
, constraint ... 
      foreign key (class_id, primary_instructor)
      references classes (class_id, primary_instructor)
          ...
, constraint ... 
      foreign key (instructor_id)
      references instructors (instructor_id)
          ...
, constraint ... CHECK (instructor_id <> primary_instructor)

So the idea is to add a super-key in classes:

(class_id, primary_instructor)

that the additional_class_instructors can reference. Then a check constraint that guarantees that primary_instructor <> instructor_id. I.e. we can't add an additional instructor in a class if he or she already is the primary_instructor for that class.

One drawback is of course that the primary instructor has to be written to the additional_class_instructor table, but that can be handled via a before trigger.

Enforce 1 or many relationship

2 Answers2