This question piggybacks off of Foreign Key to multiple tables but I thought that my elaboration could use its own subject.
Say I have the following schema (adapted from @Nathan Skerl's answer in the above link):
create table dbo.PartyType
(
PartyTypeId tinyint primary key,
PartyTypeName varchar(10)
)
insert into dbo.PartyType
values(1, 'User'), (2, 'Group');
create table dbo.Party
(
PartyId int identity(1,1) primary key,
PartyTypeid tinyint references dbo.PartyType(PartyTypeId),
unique (PartyId, PartyTypeId)
)
CREATE TABLE dbo.[Group]
(
ID int NOT NULL,
Name varchar(50) NOT NULL,
PartyTypeId as cast(2 as tinyint) persisted,
foreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
)
CREATE TABLE dbo.[User]
(
ID int NOT NULL,
Name varchar(50) NOT NULL,
PartyTypeId as cast(1 as tinyint) persisted,
foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID)
)
CREATE TABLE dbo.Ticket
(
ID int NOT NULL,
[Owner] int NOT NULL references dbo.Party(PartyId),
[Subject] varchar(50) NULL
)
And say I had another relation, say 'Stuff', where 'Users' could possess many items of 'Stuff', but it did not make sense for 'Groups' to have 'Stuff'. Could I have a Foreign Key in 'Stuff' reference just 'User'?
If it is possible, then how would one go about doing so? Or do I have to do all of my foreign keys of that sort directly through 'Party'? I got an error (There are no primary or candidate keys in the referenced table 'dbo.Users' that match the referencing column 'ID' in the foreign key) when I tried myself.
Thanks in advance!
If I understand correctly, you can do what you want. The idea is to create a unique key on PartyTypeId, Id
.
CREATE TABLE dbo.[User] (
ID int NOT NULL,
Name varchar(50) NOT NULL,
PartyTypeId as cast(1 as tinyint) persisted,
foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID),
unique (PartyTypeId, Id)
);
CREATE TABLE Stuff (
StuffId int not null primary key,
UserId int,
PartyTypeId as cast(1 as tinyint) persisted,
Foreign Key (UserId) references user(PartyTypeId, userId)
);
Here is a SQL Fiddle. This is explained in the documentation.