Unique entries in different tables-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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