Primary key and Foreign key on same column

  • 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.wordpress.com/

  • Is your table related with heirarchy mode of operations???

  • 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.wordpress.com/

  • 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!!!:-)

  • 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.wordpress.com/

  • 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...

  • 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.


    - Craig Farrell

    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

  • Thanks Craig. That's very helpful.

    - arjun

    https://sqlroadie.wordpress.com/

  • 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

  • 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'


    - Craig Farrell

    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

  • 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.wordpress.com/

  • 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?

  • 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.wordpress.com/

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply