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
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_id] FOREIGN KEY([id])
REFERENCES [dbo].[Test] ([id])
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_id]
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.