Referencial Integrity Question

Hi,

Let's say you have these 3 tables

--------------------------------------------------------------

use tempdb
go

create table TableMain (
RowID int primary key identity(1, 1)
, ItemTypeID tinyint NOT NULL
, Details varchar(200) NOT NULL
)
go

create table TableDetails (
DetailRowID int primary key identity(1, 1)
, RowID int NOT NULL
, ItemTypeID tinyint NOT NULL
, Details varchar(200) NOT NULL

)
go

create table ItemTypes (
ItemTypeID tinyint primary key identity(1, 1)
, Details varchar(50) NOT NULL
)
go

create unique nonclustered index IX_TableMain__ItemTypeID__RowID on TableMain (ItemTypeID, RowID)
go

alter table TableDetails
add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID);
go

alter table TableMain
add constraint FK_TableMain_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
go

/*
drop table TableDetails
drop table TableMain
drop table ItemTypes
*/

--------------------------------------------------------------

As you can see TableDetails references TableMain by ItemTypeID and RowID. TableMain also has a foreign key on ItemTypeID.

In this example although there is no references between TableDetails and ItemTypes on ItemTypeID field, referential integrity is still maintain because of the foreign key on TableMain (ItemTypeID, RowID)


So here is my question. Although referential integrity is maintain with this structure, would you guys still create a foreign key on TableDetails (ItemTypeID), i.e.:

alter table TableDetails
add constraint FK_TableDetails_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
go




There might not be any right or wrong, it might be a personal preference sort of thing. To me it seems performance wise it's better to not create this extra contraints since it doesn't add any additional integrity, on the other hand when looking at a DB schema this extra constraint might help understanding what's going on. Also perhaps it helps SQL Server in picking the right execution plan but that I am not sure.

Perhaps the solution is to create the constraint with a NOCHECK on it... is it?