Post reply

Duplicate Foreign Key

  • October 22, 2012 at 10:22 pm

    #268991

    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

  • October 22, 2012 at 10:49 pm

    #1551717

    Nice Quetion:)

  • October 23, 2012 at 12:15 am

    #1551728

    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

  • October 23, 2012 at 1:23 am

    #1551747

    Nice one, thank you

    Iulian

  • October 23, 2012 at 1:26 am

    #1551748

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

  • October 23, 2012 at 1:33 am

    #1551752

    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

  • October 23, 2012 at 1:33 am

    #1551753

    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

  • October 23, 2012 at 4:39 am

    #1551820

    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?

  • October 23, 2012 at 5:14 am

    #1551831

    Good question. Thanks.

  • October 23, 2012 at 5:53 am

    #1551846

    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

  • October 23, 2012 at 5:55 am

    #1551849

    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]

  • October 23, 2012 at 6:53 am

    #1551871

    When would a duplicate foreign key be used?

  • October 23, 2012 at 6:55 am

    #1551872

    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.

  • October 23, 2012 at 7:21 am

    #1551885

    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

  • October 23, 2012 at 7:28 am

    #1551893

    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