Duplicate Foreign Key

  • Lokesh Vij

    SSChampion

    Points: 10836

    Comments posted to this topic are about the item Duplicate Foreign Key

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kalyani.k478

    Default port

    Points: 1400

    Nice Quetion:)

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Nice one, thank you

    Iulian

  • rati0090

    SSC Journeyman

    Points: 84

    gud 1 but it should be 253 as their is a limit of 253

  • Lokesh Vij

    SSChampion

    Points: 10836

    rati0090 (10/23/2012)


    gud 1 but it should be 253 as their is a limit of 253

    This is the recommended maximum limit, but you can have infinite number of FK constraints in such scenario. BOL has specifically mentioned the reason for this recommended max limit: "Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process"

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Stuart Davies

    SSCoach

    Points: 18878

    Nice question - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • call.copse

    SSCoach

    Points: 17201

    I liked this question, as I wondered whether anyone would try running the sample code, and what would happen if they did (and I got it right comfortably!). Would you get to some kind of out of resource type scenario do you think?

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Good question. Thanks.

  • Lokesh Vij

    SSChampion

    Points: 10836

    call.copse (10/23/2012)


    I liked this question, as I wondered whether anyone would try running the sample code, and what would happen if they did (and I got it right comfortably!). Would you get to some kind of out of resource type scenario do you think?

    🙂 :hehe:

    Ah...atlast some one got the point why I intentionally pasted this sample code in the problem

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • vk-kirov

    SSCertifiable

    Points: 7686

    call.copse (10/23/2012)


    I liked this question, as I wondered whether anyone would try running the sample code, and what would happen if they did (and I got it right comfortably!). Would you get to some kind of out of resource type scenario do you think?

    After answering the question, I ran the sample code. After 10 minutes I canceled it and I got a table with 5679 foreign keys. After that, I tried to insert a record into that table: "INSERT INTO childTbl (parent_id) VALUES (1)". SQL Server was "thinking" for 22 minutes and failed with the following message:

    [font="Courier New"]Msg 8621, Level 17, State 1, Line 1

    The query processor ran out of stack space during query optimization. Please simplify the query.[/font]

  • wdolby

    Old Hand

    Points: 365

    When would a duplicate foreign key be used?

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Lokesh Vij (10/23/2012)


    call.copse (10/23/2012)


    I liked this question, as I wondered whether anyone would try running the sample code, and what would happen if they did (and I got it right comfortably!). Would you get to some kind of out of resource type scenario do you think?

    🙂 :hehe:

    Ah...atlast some one got the point why I intentionally pasted this sample code in the problem

    And I am still missing the point of this reply.

  • WayneS

    SSC Guru

    Points: 95386

    table can contain unlimited number of duplicate foreign keys, though the recommended maximum is 253

    So, we're now recommending building duplicate foreign keys?

    Personally, I thought that this is essentially a useless question. Interesting, sure. Useful? Nope.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • vk-kirov

    SSCertifiable

    Points: 7686

    wdolby (10/23/2012)


    When would a duplicate foreign key be used?

    I think there's no practical purpose in such duplicate foreign keys. Some RDBMSs even do not allow these FKs. For example, you'll get an error message "ORA-02275: such a referential constraint already exists in the table" if you run the following code in Oracle:

    CREATE TABLE parentTbl ( id INT UNIQUE );

    CREATE TABLE childTbl ( child_id INT UNIQUE, parent_id INT);

    ALTER TABLE childTbl ADD CONSTRAINT FK1 FOREIGN KEY (parent_id) REFERENCES parentTbl(id);

    ALTER TABLE childTbl ADD CONSTRAINT FK2 FOREIGN KEY (parent_id) REFERENCES parentTbl(id);

    The tables parentTbl, childTbl and constraint FK1 will be created, while the creation of FK2 will fail.

Viewing 15 posts - 1 through 15 (of 27 total)

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