1

Say for example I have a table for Person. This table looks as such:

create table Person (id int not null primary key, 
FirstName varchar(20) not null,
LastName varchar(20) not null
)

The ISA Hierarchy has 2 extended types, Staff and Student, underneath it as follows:

create table Staff (
id int not null primary key references Person(id),
department varchar(20) not null
)
create table Student (
id int not null primary key references Person(id),
course varchar(20) not null
)

From this current implementation, I could possibly create a Student entry and Staff entry both related to the same Person ID. I'm trying to make it exclusive so that a Person is only either allowed to be Staff or Student but I'm a bit lost on how to do that.

Any input is appreciated !

KrabbyPatty
  • 312
  • 1
  • 2
  • 9
  • One way to achieve this is via BEFORE INSERT Triggers on the Staff & Student tables. And let those triggers check if a flag field in the Person table is already filled in for the ID. F.e. a field named "Type". Also with AFTER INSERT Triggers that update the flag field in Persons. – LukStorms Oct 30 '21 at 17:38

1 Answers1

0

In the real world, somebody could be both a staff member and a student, but you are describing a situation in which somebody may be only one or the other. Consequently the 'staff or student' category is a single-valued attribute of the person, and therefore this category value should be stored in the Person table. To constrain the child tables, the Person table should have an additional unique index on both id and that category column, and your Staff and Student tables then should also include this column, and use it in the foreign key back to Person, and have a check constraint to ensure that only the appropriate value (and therefore, category of person) is entered into each of the child tables.

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18