Unique entries in different tables

Question

I have a person table with a primary key called id. I want 2 other tables called teacher_table and student_table which both have foreign keys referenced to a person.id.

How can I make sure a student can't be a teacher?

Edit: Is it convention to do these sorts of contrains in a stored procedure or something? because what if you get very complicated relationships, just the table constraints can't do that right?

Yes, but thern everytime I'd want a specific teacher, I'd need to go trough the full person table so that wouldn't be an option... — Vexea, Nov 27 '21 at 17:46
This sounds like a normalization issue. Your problem can be avoided by simply having 1 general table for all "users" (or whatever scenario you're constructing) and the other 2 tables, `tbl_teachers` & `tbl_students`, will be deciding what type of user they are. If the user ID from the users table exists in the teachers table, they're a teacher. If the user ID exists in the students table, then they're a student. This logic will also be very future proof and expandable, should you want more logic in the future. It keeps things "abstract". — Martin, Nov 27 '21 at 17:47
Can be done in Oracle or PostgreSQL. What database are you using? Also, can a `person` be abstract? that is, having no related row in `teacher` o `student`. — The Impaler, Nov 27 '21 at 17:48
Does this answer your question? [Enforcing exclusivity in table inheritance: composite foreign key vs check constraint](https://stackoverflow.com/questions/24025167/enforcing-exclusivity-in-table-inheritance-composite-foreign-key-vs-check-const) — Serg, Nov 27 '21 at 17:59

score 1 · Answer 1 · answered Nov 27 '21 at 18:11

The simple solution allows abstract person rows that may not have corresponding concrete rows in student or teacher.

You'll need a composite key on the main table, and should take the form:

create table person (
  id int primary key not null,
  type char(1) not null check (type in ('T', 'S')),
  unique (id, type)
  -- common properties here
);
 
create table teacher (
  id int not null,
  type char(1) check (type ='T'),
  foreign key (id, type) references person (id, type)
  -- teacher's properties here
);

create table student (
  id int not null,
  type char(1) check (type ='S'),
  foreign key (id, type) references person (id, type)
  -- student's properties here
);

If you need to enforce all entities are concrete, that can be done in Oracle or PostgreSQL by including a reverse foreign key constraint.

Yes, this seems good, but I'll definitly look into the reverse foreign key contraint! — Vexea, Nov 27 '21 at 18:39

LukStorms · Answer 2 · 2021-11-27T20:23:42.647

You could put some BEFORE INSERT triggers on the student & teacher tables.

Let the triggers check if a certain flag in the persons table corresponds with the expected flag for that table.

The example below works only in a Postgresql RDBMS.

create table person (
 id serial primary key, 
 first_name varchar(30), 
 last_name varchar(100), 
 person_type char(1) not null check (person_type in ('T','S')), 
 unique (first_name, last_name)
);

create table student_table (
 student_id serial primary key, 
 person_id int, 
 grade int, 
 foreign key (person_id) references person(id), 
 unique (person_id)
);

create table teacher_table (
 teacher_id serial primary key, 
 person_id int, 
 nickname varchar(30), 
 foreign key (person_id) references person(id), 
 unique (person_id)
);

insert into person (person_type, first_name, last_name) values 
  ('S', 'Anna', 'Brandi')
, ('T', 'Bob', 'Modest')

Creating a UDF for the triggers to use

CREATE FUNCTION check_person_type()  
RETURNS trigger 
AS $check_person_type$
DECLARE current_person_type char(1);
DECLARE new_person_type varchar(1);
    BEGIN
        
        new_person_type := TG_ARGV[0];
        
        SELECT INTO current_person_type person_type
        FROM person 
        WHERE id = NEW.person_id;
        
        -- teacher check
        IF current_person_type = 'T' AND new_person_type != 'T' THEN
            RAISE EXCEPTION 'Person with id % is a teacher', NEW.person_id;
        END IF;

        -- student check
        IF current_person_type = 'S' AND new_person_type != 'S' THEN
            RAISE EXCEPTION 'Person with id % is a student', NEW.person_id;
        END IF;
        
        RETURN NEW;
    END;
$check_person_type$ LANGUAGE plpgsql;

The triggers

CREATE TRIGGER check_student_person_type BEFORE INSERT ON student_table
    FOR EACH ROW EXECUTE PROCEDURE check_person_type('S');

CREATE TRIGGER check_teacher_person_type BEFORE INSERT ON teacher_table
    FOR EACH ROW EXECUTE PROCEDURE check_person_type('T');

Doing something bad

-- Trying to enroll teacher Bob as a student
insert into student_table (person_id, grade)
select id, 99 as grade
from person 
where (first_name, last_name) = ('Bob', 'Modest');

ERROR:  Person with id 2 is a teacher

CONTEXT: PL/pgSQL function check_person_type() line 14 at RAISE

-- Trying to enroll Anna as a teacher
insert into teacher_table (person_id, nickname)
select id, 'sweety pie' as nickname
from person 
where (first_name, last_name) = ('Anna', 'Brandi');

ERROR:  Person with id 1 is a student

CONTEXT: PL/pgSQL function check_person_type() line 19 at RAISE

Trying better this time

insert into student_table (person_id, grade)
select id, 1 as grade
from person 
where (first_name, last_name) = ('Anna', 'Brandi');
select * from student_table limit 1;


student_id | person_id | grade
---------: | --------: | ----:
         2 |         1 |     1

insert into teacher_table (person_id, nickname)
select id, 'the goat' as nickname
from person 
where (first_name, last_name) = ('Bob', 'Modest');
select * from teacher_table limit 1;


teacher_id | person_id | nickname
---------: | --------: | :-------
         2 |         2 | the goat

db<>fiddle here

Seems like the OP is interested on ensuring data cannot violate the rules when storing it. — The Impaler, Nov 27 '21 at 17:52

Unique entries in different tables

2 Answers2