April 19, 2013 at 10:39 am
I've recently run across this structure in a database that I'm migrating.
create table fktest (
dept int not null,
dps int not null,
constraint [pkfktest] primary key clustered (dept,dps),
constraint [fktester] foreign key (dept, dps) references fktest (dept,dps)
) on [Primary]
Is there a logical reason to have a foreign key that references itself when it's also the primary key in a table? Is this used for some reason? It just doesn't make sense to me.
Thoughts?
April 19, 2013 at 10:52 am
The structure is only part of the picture, you need to look at the data as well.
April 19, 2013 at 10:57 am
Ok, Lynn, let me go pull some test data and post it. I can't, for the life of me, think of any reason why someone would build this structure.
April 19, 2013 at 11:06 am
I just don't see the practical application of this foreign key. What benefit does it provide? I'll add that this is a user created database and the user is not around to answer this question any more.
Slight modification to table structure:
create table fktest (
dept varchar(10) not null,
dps varchar(10) not null,
constraint [pkfktest] primary key clustered (dept,dps),
constraint [fktester] foreign key (dept, dps) references fktest (dept,dps)
) on [Primary]
insert into fktest
VALUES('FAN', '3553'),
('FAN', '3554'),
('HAMS', '00001'),
('HAMS', '0001'),
('HAMS', '0002'),
('HAMS', '0003'),
('HAMS', '1304'),
('HAMS', '1336'),
('HAMS', '1337'),
('HAMS', '1350'),
('HAMS', '1355'),
('HAMS', '1362'),
('HAMS', '1363'),
('HAMS', '3104'),
('HAMS', '3106'),
('HAMS', '3108'),
('HAMS', '3110'),
('HAMS', '3540'),
('HAMS', '3541'),
('HAMS', '3542'),
('HAMS', '3543'),
('HAMS', '3544'),
('HAMS', '3545'),
('HAMS', '3555'),
('HAMS', '6000'),
('HAMS', '6001'),
('HAMS', '6002'),
('HAMS', '6005'),
('HAMS', '6006'),
('HAMS', '6007'),
('HAMS', '6008'),
('HAMS', '6009'),
('HAMS', '6010'),
('HAMS', '6011'),
('HAMS', '6012'),
('HAMS', '6013'),
('HAMS', '6014'),
('HAMS', '6015'),
('HAMS', '6016'),
('HAMS', '6017'),
('HAMS', '6018'),
('HAMS', '6019'),
('HAMS', '6020'),
('HAMS', '6022'),
('HAMS', '6023'),
('HAMS', '6024'),
('HAMS', '6025'),
('HAMS', '6028'),
('HAMS', '6029'),
('HAMS', '6030')
SELECT * FROM fktest
drop table fktest
April 19, 2013 at 11:11 am
If that is the actual table structure, there seems to be no reason for the foreign key reference, since it is the primary key referencing itself.
At least you won't get any FK constraint violations. 🙂
April 19, 2013 at 11:13 am
Maybe the user just wanted to see if it could be done? Other than that, based on the structure and the data I see no reason for th FK.
April 19, 2013 at 11:17 am
Excellent.. thank you folks.. I thought there must be some sort of fundamental flaw in my understanding.. lol
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply