July 13, 2009 at 6:46 am
Hello,
I have a question, how can I create a self-referencing table if the primary key is made up out of a composite key? (2 fields)
Example:
Area [PK]
ID [PK]
ParentID [*]
Name
Description
I want to create a self-referencing constraint between ParentID and ID ...
:hehe:
July 13, 2009 at 7:02 am
To the best of my recollection, you canNOT normally create FK constraints for self-referencing relations, because sometimes the ParentID has to be NULL which cannot effectively be handled by FK constraints.
SQL Server 2008 has a new feature (filtered indexes) which might allow you to get around this (sorry, I cannot remember for sure if that works).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 13, 2009 at 9:23 am
If the PK consists of two columns, the FK must as well. You couldn't do it exactly as you've defined. You'd also need a ParentArea column.
If the ID column is unique, why not just make it the PK, all by itself. If you have to leave it as a compound key (I'd be curious why) you could create a unique constraint on the ID column and then it's possible to create a relationship to it that way. But if the ID column is not unique, you really have no option but to reference both columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2009 at 1:35 pm
RBarryYoung (7/13/2009)
To the best of my recollection, you canNOT normally create FK constraints for self-referencing relations, because sometimes the ParentID has to be NULL which cannot effectively be handled by FK constraints.
Actually, SQL handles NULL FKs just fine:CREATE TABLE MyTable
(
Area INT NOT NULL,
ID INT NOT NULL,
ParentArea INT NULL,
ParentID INT NULL,
Name VARCHAR(50),
[Description] VARCHAR(50),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
Area,
ID
)
)
ALTER TABLE MyTable
ADD CONSTRAINT FK_MyTable_ParentArea_ParentID FOREIGN KEY
(ParentArea, ParentID)
REFERENCES MyTable
(Area, ID)
Area INT NOT NULL,
ID INT NOT NULL,
ParentArea INT NULL,
ParentID INT NULL,
Name VARCHAR(50),
[Description] VARCHAR(50),
INSERT MyTable
SELECT 1, 1, NULL, NULL, 'Name1', 'Desc1'
UNION ALL SELECT 2, 1, 1, 1, 'Name2', 'Name2'
UNION ALL SELECT 2, 2, 1, 1, 'Name3', 'Name3'
UNION ALL SELECT 3, 5, 2, 2, 'Name4', 'Name4'
SELECT *
FROM MyTable
-- Fails
INSERT MyTable
SELECT 5, 5, 3, 2, 'Name5', 'Name5'
DROP TABLE MyTable
July 13, 2009 at 3:54 pm
I stand corrected... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply