Duplicate Foreign Key

  • 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

  • Nice Quetion:)

  • 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

  • Nice one, thank you

    Iulian

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

  • 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

  • Nice question - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There 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

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

  • Good question. Thanks.

  • 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

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

  • When would a duplicate foreign key be used?

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

  • 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

  • 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 26 total)

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