Foreign key reference to a specific subtype

Question

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!

score 1 · Accepted Answer · answered Jul 25 '15 at 03:00

1

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.

answered Jul 25 '15 at 03:00

Gordon Linoff

1,242,037
58
646
786

Thanks you for the very helpful answer, and the attached links. Helped my understanding too. Couple of things: code above doesn't match the Fiddle; in fact the code above generates a "Number of referencing columns in foreign key differs from number of referenced columns, table 'Stuff'." error. – daner Jul 27 '15 at 17:42
Second, I was wondering about the pros/cons of asserting uniqueness on just the ID field in User, and then having the FK in Stuff be just the UserId field (thus dropping the PartyTypeId field) – daner Jul 27 '15 at 17:45

score 0 · Answer 2 · answered Jul 25 '15 at 04:22

0

Yes, you can have a foreign key in Stuff reference just User:

CREATE TABLE Stuff (
    StuffId int not null primary key,
    UserId int,
    Foreign Key (UserId) references dbo.[User](ID)
);

answered Jul 25 '15 at 04:22

reaanb

9,806
2
23
37

Foreign key reference to a specific subtype

2 Answers2