May 2, 2011 at 3:26 am
Hi folks, my boss asked me an interesting question today.
Boss: Arjun, if I add a PK on a column and add a FK on the same column, what good does it do?
Arjun: Huh? That does not make sense. I'm not sure that is even possible. Let me try it out.
Boss: Try it, you can define constraints like that.
Arjun: Errr, this will be tricky.
So, I created a table.
CREATE TABLE [dbo].[Test]
(
[id] [smallint] NOT NULL,
[char] [nchar](10) NOT NULL,
[num] [numeric](18, 0) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_id] FOREIGN KEY([id])
REFERENCES [dbo].[Test] ([id])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_id]
GO
id column is the primary key and there is a foreign key defined on Test.id -- Test.id.
What is the significance of this foreign key relationship? I was able to do inserts/updates/deletes normally. I believe that it (the FK) does not serve any purpose. Am I right?
Any help will be greatly appreciated.
- arjun
https://sqlroadie.com/
May 2, 2011 at 3:39 am
Is your table related with heirarchy mode of operations???
May 2, 2011 at 3:42 am
No, even if it was related to a hierarchy, the FK will be on a different column.
Typically, something like this (just the column names for simplicity)
EmployeeID smallint --PK
--
--
--
SupervisorID smallint --FK on EmployeeID (aka self referencing FK)
Please correct me if I am wrong.
And, thanks for the quick reply 🙂
- arjun
https://sqlroadie.com/
May 2, 2011 at 3:51 am
Just a later thought, how your manager would have thought of the same....you may explain to us about the context too....
I was just thinking something a tree structure...
1
1 10
1 10 100
Lower levels always satisfy a higher node (here 1 is the higher node.)
Apart, am not able to think of any useful mapping right now. Probably will wait for some big heads!!!:-)
May 2, 2011 at 4:02 am
Ok, thanks buddy. I do not understand what you mean by the tree structure in this context though.
I guess this was just a theoretical question from my boss. I was not aware that a FK can be added on the PK column itself. So, now that I have tried it out and found that it can be done, I want to know in what scenario such a FK can be used.
Table: Test
Columns: [ID], [CHAR], [NUM]
PK is defined on ID. And a FK is defined on ID.
How can referential integrity be evaluated in this case?
- arjun
https://sqlroadie.com/
May 2, 2011 at 4:16 am
Ohhh my bad, second time, i dint think abt the primary key set on it!!!
However I could not see any difference between the foreign key and without foreign keys in XML plans for a small queries atleast...
May 2, 2011 at 11:26 am
You're correct, it's valid, but pointless. SQL doesn't stop you from doing it, but it has no effect.
Basically you're saying make sure my ID exists when my ID exists. No real use to it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2011 at 11:41 am
Thanks Craig. That's very helpful.
- arjun
https://sqlroadie.com/
May 2, 2011 at 12:29 pm
Craig Farrell (5/2/2011)
You're correct, it's valid, but pointless. SQL doesn't stop you from doing it, but it has no effect.Basically you're saying make sure my ID exists when my ID exists. No real use to it.
Not only that but it will prevent you from doing any deletes until you remove the foreign key constraint.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
May 2, 2011 at 12:48 pm
ricer (5/2/2011)
Craig Farrell (5/2/2011)
You're correct, it's valid, but pointless. SQL doesn't stop you from doing it, but it has no effect.Basically you're saying make sure my ID exists when my ID exists. No real use to it.
Not only that but it will prevent you from doing any deletes until you remove the foreign key constraint.
Incorrect:
CREATE TABLE fk_test (tID INT IDENTITY( 1, 1) NOT NULL, someData VARCHAR(50) NULL, CONSTRAINT PK_tmp PRIMARY KEY CLUSTERED (tID))
INSERT INTO fk_test (someData) VALUES ('abc')
INSERT INTO fk_test (someData) VALUES ('def')
ALTER TABLE fk_test ADD CONSTRAINT FK_tID FOREIGN KEY (tID) REFERENCES fk_test (tID)
DELETE FROM fk_test where someData = 'abc'
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2011 at 12:50 pm
ricer (5/2/2011)
Craig Farrell (5/2/2011)
You're correct, it's valid, but pointless. SQL doesn't stop you from doing it, but it has no effect.Basically you're saying make sure my ID exists when my ID exists. No real use to it.
Not only that but it will prevent you from doing any deletes until you remove the foreign key constraint.
That's not true Will. Please try inserting some rows to the Test table (you can find the DDL in the 1st post on the thread). If you write a delete statement, you can see that the row(s) will get deleted. This is because foreign key and primary key refer to the same row.
What you have said is true in the case of all other kinds of FK constraints.
- arjun
https://sqlroadie.com/
May 4, 2011 at 2:53 pm
It’s fairly common to have a Primary Key and a Foreign Key on the same column, when the Foreign Key points to a different table. Example: a company where some employees are authorized buyers, and you keep several columns of buyer-specific data in a BUYER table. EmployeeId is the Pk of both the Employee table and the Buyer table. In addition, the EmployeeId column in the Buyer table is a FK pointing to the Employee table.
Could this have been what was meant?
May 4, 2011 at 3:24 pm
mthurber (5/4/2011)
It’s fairly common to have a Primary Key and a Foreign Key on the same column, when the Foreign Key points to a different table. Example: a company where some employees are authorized buyers, and you keep several columns of buyer-specific data in a BUYER table. EmployeeId is the Pk of both the Employee table and the Buyer table. In addition, the EmployeeId column in the Buyer table is a FK pointing to the Employee table.Could this have been what was meant?
Hi, thanks for the post. But, that's not what I meant.
In the case you mentioned, there are two tables - Employee & Buyer. Employee.EmployeeID is a PK. Buyer.EmployeeID is a PK and a FK to Employee.EmployeeID. Note that the FK and PK are two different columns here - Employee.EmployeeID and Buyer.EmployeeID.
I like the way you thought it out though. Thanks buddy.
- arjun
https://sqlroadie.com/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply