Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

reason for this??? Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 10:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:20 PM
Points: 541, Visits: 1,047
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?
Post #1444512
Posted Friday, April 19, 2013 10:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
The structure is only part of the picture, you need to look at the data as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444518
Posted Friday, April 19, 2013 10:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:20 PM
Points: 541, Visits: 1,047
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.
Post #1444520
Posted Friday, April 19, 2013 11:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:20 PM
Points: 541, Visits: 1,047
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

Post #1444526
Posted Friday, April 19, 2013 11:11 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 3,135, Visits: 11,482
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.



Post #1444530
Posted Friday, April 19, 2013 11:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444532
Posted Friday, April 19, 2013 11:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:20 PM
Points: 541, Visits: 1,047
Excellent.. thank you folks.. I thought there must be some sort of fundamental flaw in my understanding.. lol
Post #1444534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse