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,

How long did you spend creating those tidy ASCII tables for this question?!! — Mark Byers, Aug 27 '12 at 16:53
In my "similar" design, I have a "People" (e.g. person information) and "Faculty" and "Student" tables (roles). Thus People->Faculty->Course(Instructor) .. — , Aug 27 '12 at 16:58
One option is to handle all changes to the table through a stored procedure. The procedure can enforce your constraint. You can use rights to deny insert/update/delete access to anyone except the stored procedure. — Andomar, Aug 27 '12 at 17:05
How do you dinstinguish a normal person from a teacher with that model? (e.g. in the `person` table?) — , Aug 27 '12 at 17:20
This is not an attempt to point you to an answer, just a note: why is there a `B_Occupation_Person` table in your schema and not just an `Occupation_ID` column in `Person`? Do you allow a person to have more than one occupation? — Andriy M, Aug 27 '12 at 17:22
You might find this [question](http://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes) regarding subtypes helpful. — Tim Lentine, Aug 27 '12 at 17:30
@MarkByers, I'm not sure if this was what was used but [this amazing tool](http://www.asciiflow.com/#Draw) was the subject of a recent `[feature-request]` on [meta](http://meta.stackexchange.com/questions/145030/digrams-in-questions-answers). — Ben, Aug 27 '12 at 18:37
@MarkByers I used [this tool](http://www.sensefulsolutions.com/2010/10/format-text-as-table.html), which I found via Google and the words "stackoverflow format sql table". — Rodolphe, Aug 28 '12 at 07:25
@AndriyM You're totally right. In my real DB, I don't have this useless binding table but even with this mistake, here, the question remains, so I won't update my question. — Rodolphe, Aug 28 '12 at 07:26
@Andomar I'll keep that in mind but I tend to avoid stored procedure if possible, because it's not easy to debug. — Rodolphe, Aug 28 '12 at 07:28
@a_horse_with_no_name This model is my real one. As @AndryM pointed out, there should be an `Occupation_ID` column in the `Person` table. — Rodolphe, Aug 28 '12 at 07:30
@TimLentine Thanks for the link. I read the (very long) answer and it indeed seems to answer to my problem but in the end, a UDF is still used. — Rodolphe, Aug 28 '12 at 08:44

score 1 · Accepted Answer · 2012-08-27T18:35:39.117

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).

Adding `person_type` as a property of a many-to-many relation violates normal form. Not sure that's a good idea. — Andomar, Aug 28 '12 at 07:42
@Andomar: in general adding a column to a many-to-many relation does *not* (necessarily) violate 3NF because you might actually want to have an attribute describing the *type* of the relationship. But you are right that my example does violates 3NF but it's because of the PK in the `person` table (because not all attributes in there depend on the PK). But in this case I don't see a different way of enforcing such a requirement with statically declared constraints (as opposed to triggers) — , Aug 28 '12 at 07:57
Agreed. I just think this amount of bending static constraints is a bad idea, and the business logic should be enforced somewhere else. — Andomar, Aug 28 '12 at 08:37

score 0 · Answer 2 · answered Aug 27 '12 at 17:19

Your question here represents a form of business logic. You not only have the issue of making sure that b_course_person only contains teachers at the time of insertion, but also that the data remains consistent - ie: if a person stops becoming a teacher, what happens to the data in b_course_person then?

As such, you should implement this logic in code; either as a stored procedure that represents your interface to the database and ensures that all data changes result in a consistent, valid set of data, or further up the layers of your application in a business logic layer.

SQL check constraint without UDF

2 Answers2