Implementing Table Inheritance in SQL ServerBy Jeff Smith on 20 February 2008 http://www.sqlteam.com/When designing a database, we sometimes come acr......
Then, we simply create a foreign key relation back to the People table – but instead of just on the PersonID column, we create the constraint on both the PersonID and the PersonTypeID columns. With these constraints in place, we are guaranteed that Person will only have one type, and that they can only have data in the matching sub-table for that type.
There is a catch, however: we cannot create our sub-tables just yet; if we try it, we’ll get the following error:
create table Students
(
PersonID int primary key,
PersonTypeID as 1 persisted, -- student
EnrollmentDate datetime,
foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
)
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'People' that
match the referencing column list in the foreign key 'FK__Students__1F98B2C1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
This is because the unique constraint on the People table is just on the PersonID column, but we are trying to set up a foreign key constraint on the combination of PersonID/PersonTypeID. To handle this, we simply add an additional unique constraint to the People table, covering both PersonID and PersonTypeID:
drop table People
go
create
6/10 首页 上一页 4 5 6 7 8 9 下一页 尾页 |
