SQL check constraint without UDF

Question

I have the following (fictitious) tables:

╔════════════════════╗        ╔════════════════════╗
║ Course             ║        ║ Person             ║
╠══════╦═════════════╣        ╠══════╦═════════════╣
║ ID   ║ int         ║        ║ ID   ║ int         ║
║ Name ║ varchar(50) ║        ║ Name ║ varchar(50) ║
╚══════╩═════════════╝        ╚══════╩═════════════╝

╔════════════════════╗        ╔═════════════════════╗
║ Occupation         ║        ║ B_Occupation_Person ║
╠══════╦═════════════╣        ╠══════════════╦══════╣
║ ID   ║ int         ║        ║ Person_ID    ║ int  ║
║ Name ║ varchar(50) ║        ║ Ocupation_ID ║ int  ║
╚══════╩═════════════╝        ╚══════════════╩══════╝

╔═════════════════╗
║ B_Course_Person ║
╠═══════════╦═════╣
║ Course_ID ║ int ║
║ Person_ID ║ int ║
╚═══════════╩═════╝

In the Occupation table, there are 2 rows: Student and Teacher.

The B_Occupation_Person binding table allows me to give all the persons an occupation and the B_Course_Person binding table allows me to associate a teacher with a course.

My problem is I'd like to ensure that B_Course_Person could only contain teachers.

My first idea was to add a check constraint on this table but I can only do it by using a UDF to get the person's occupation from the B_Occupation_Person table. And from what I read here, it's bad to use a UDF in a check constraint.

My second idea was to add a column Occupation in the B_Course_Person table but then I get data redundancy...

What's the best way, here?

Thanks,

Answer 1

If you have a "type" column in your person table to distinguish students from teachers (which is e.g. not possible if a person can be both) you can include that type column in the primary key, and then restrict the foreign key the link table to teachers:

create table person 
(
   id integer not null, 
   person_type varchar(10) not null,
   name varchar(100),
   constraint pk_person primary key (id, person_type),
   constraint type_check check (person_type in ('student', 'teacher'))
);

create table b_occupation_person
(
  occupation_id integer not null,
  person_id integer not null,
  person_type varchar(10) not null,
  constraint fk_occupation_person 
      foreign key (person_id, person_type) 
      references person (id, person_type),
  constraint type_check check (person_type = 'teacher')
);

The person_type is redundant in the b_occupation_person but as far as I can tell it's the only option to create this type of constraint in a declarative manner.

Due to the foreign key and the check constraint it's not possible to insert anything else but a teacher into b_occupation_person.

But again: this only works if you actually can distinguish teachers from students (and if a teacher cannot be a student).

If you need a person to be teacher and student (and you don't have a "person_type"), you might think about a teacher table that simply references the person table:

create table person 
(
   id integer not null primary key, 
   name varchar(100)
);

create table teacher 
(
  person_id integer not null primary key,
  foreign key (person_id) references person (id)
);

create table b_occupation_person
(
  occupation_id integer not null,
  teacher_id integer not null,
  foreign key (teacher_id) references teacher (person_id)
);

The drawback of this is that a person that is teacher needs to be inserted twice (once into person, once into teacher).

In PostgreSQL you could take advantage of table inheritance and define teacher to inherit from person. Thus any insert into teacher would automatically create a person as well (so you don't need to insert such a person twice).