Setting a foreign key to more than one table

问题:

I have three tables Teachers, Student, ViewType

table Teachers{
  Id uniqueidentifier, 
  Name nvarchar
}


table Student{
  Id uniqueidentifier, 
  Name nvarchar
}


table ViewType{
  Id uniqueidentifier, 
  Type String
}

Note: Let's say for the example that ViewType is not a regular look up table.
it contains data of how to present the Teacher or Student in the ui therefor shouldn't be in the Teacher or Studenttable model.

Is there a way to create a foreign key to two tables where a key is enforced to from and only from the two tables? Thanks.

回答1:

Not with Declarative Referential Integrity constraints.

You would have to implement this with triggers; and you would need them on all three tables (insert + update on ViewType, delete + update on the others).

You could put the constraints the other way:

alter table Student add constraint FK foreign key (Id) references ViewType (Id)
alter table Teachers add constraint FK foreign key (Id) references ViewType (Id)

It isn't perfect (you could end up with a Student & Teacher referencing the same Id, which you would have to deal with), but it's probably the best you could do.