SQL: Foreign key that is a child of another foreig

问题:

I'd like to ask regarding a particular issue I have.

For example, I have a Student table that has a SchoolID and a ClassID, both of which are foreign keys to the School and Classroom tables respectively. The Class table has a SchoolID column which is a foreign key to the School table.

Is there any way I can ensure that the ClassID in the Student table is a child of the SchoolID, or must I remove the SchoolID and just live with the ClassID in order to prevent any possible discrepancies?

回答1:

Yes, technically you should remove the Student.SchoolId foreign key, if Class already has SchoolId, as this already defines the relationship between Student and School.

That said, there are some common 'exceptions' to this, e.g.:

1. If the relationship between Student and School can mean something different than the relationship between Class and School (e.g. if the student goes to School X as 'his/her main school', but then also attends an extra mural Class Y at School Z). (Actually, this could highlight a modelling error, i.e. Student : Class might actually be a many to many relationship)

2. For performance reasons it is sometimes (a somewhat reluctant) choice to add the parent's foreign key to a child to avoid the need to join back to parent. But as you say, this can result in integrity issues.

回答2:

Yes, you can enforce it by having multiple columns in your foreign key reference to the Classes table:

create table Schools (
    SchoolID int not null primary key,
    Name varchar(30) not null unique
)
go
create table Classes (
    ClassID int not null primary key,
    SchoolID int not null foreign key references Schools (SchoolID),
    Name varchar(30) not null,
    constraint UQ_Class_Schools UNIQUE (ClassID,SchoolID),
    constraint UQ_Class_Names UNIQUE (SchoolID,Name)
)
go
create table Students (
    StudentID int not null primary key,
    SchoolID int not null foreign key references Schools (SchoolID),
    ClassID int not null,
    Name varchar(95) not null,
    constraint FK_Student_Classes FOREIGN KEY (ClassID,SchoolID) references Classes (ClassID,SchoolID)
)

Depending on taste, you may decide to also declare a foreign key just on the ClassID column in both tables.

回答3:

The Student table should reference Class.

create table Student (
  ...
  SchoolID integer not null,
  ClassID integer not null,
  foreign key (SchoolID, ClassID) references Class (SchoolID, ClassID),
  ...
);

In turn, the Class table should reference School.