Foreign key

  • bitbucket-25253 (5/17/2012)


    Well my answer was declared to be incorrect, so went to read the explanation, following the link given ..... nada / nothing / not a word to support what is deemed the correct answer.

    So went and entered the code in SQL 2008R2, triple checked what I had entered and it matched what was given in the question. When pressing the F5 key for SSMS (2008R2) .. and again using SSMS checked after the error message and what do you know ... what was shown did NOT match what was give as the correct answer.

    Hmmmmmmmmmmm

    That's very intrigueing. Can you copy and paste the code you tried, and copy/paste the output you received? That makes it a bit easier to try and find what went wrong.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • bitbucket-25253 (5/17/2012)


    So went and entered the code in SQL 2008R2, triple checked what I had entered and it matched what was given in the question. When pressing the F5 key for SSMS (2008R2) .. and again using SSMS checked after the error message and what do you know ... what was shown did NOT match what was give as the correct answer.

    I ran the code in 2008R2 (with changes only to the names of the tables and columns - i.e. "__" at beginning of table name so it would be top of list when created and thus easier to delete when done), and I got the results given in the QOTD.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Running the code gives me the correct answer, after I fixed my typo.

    I was fully aware of what would happen here... as I've generated this error quite a few times in my life. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • The answer I was looking for was "create 2 tables with error for third", that NOT being an option I chose incorrectly the "create 3 tables with error for third". Trick question. The real answer was not even an option. I'm not a fan of trick answers. Good question though.

  • I think the assumption in the example is that all fields are being created as NOT NULL by default.

    Table 3 doesn't get created because there is no PK defined on Table2, you can't reference another table without their being a PK.

    Making the assumption we have non-nullable fields, we can say that Table1 was created and Table2 was created allowing the reference to be established. So the answer had to be two tables.

    Ad maiorem Dei gloriam

  • Hugo Kornelis (5/18/2012)


    bitbucket-25253 (5/17/2012)


    Well my answer was declared to be incorrect, so went to read the explanation, following the link given ..... nada / nothing / not a word to support what is deemed the correct answer.

    So went and entered the code in SQL 2008R2, triple checked what I had entered and it matched what was given in the question. When pressing the F5 key for SSMS (2008R2) .. and again using SSMS checked after the error message and what do you know ... what was shown did NOT match what was give as the correct answer.

    Hmmmmmmmmmmm

    That's very intrigueing. Can you copy and paste the code you tried, and copy/paste the output you received? That makes it a bit easier to try and find what went wrong.

    Thank you for the offer to help, but after testing, I simply "gave up" and closed SSMS NOT saving the code that I had entered.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Bill Wehnert (5/18/2012)


    Table 3 doesn't get created because there is no PK defined on Table2, you can't reference another table without their being a PK.

    It does not have to be PK. You can define it to refer to columns of a UNIQUE constraint as well.

  • Bill Wehnert (5/18/2012)


    I think the assumption in the example is that all fields are being created as NOT NULL by default.

    Table 3 doesn't get created because there is no PK defined on Table2, you can't reference another table without their being a PK.

    Making the assumption we have non-nullable fields, we can say that Table1 was created and Table2 was created allowing the reference to be established. So the answer had to be two tables.

    You don't need any assumption like that.

    The only field that needs to be not null is the primary key of the first table, so there's no question of needing a not null default for "all" fields.

    And the column definition for that one field that does need to be not null includes the keyword "primary key" which, when written as part of a column definition, implies not null; so no question of a default being required for that either.

    Tom

  • This was removed by the editor as SPAM

  • Good question, thanks.

  • Bill Wehnert (5/18/2012)


    I think the assumption in the example is that all fields are being created as NOT NULL by default.

    Table 3 doesn't get created because there is no PK defined on Table2, you can't reference another table without their being a PK.

    Making the assumption we have non-nullable fields, we can say that Table1 was created and Table2 was created allowing the reference to be established. So the answer had to be two tables.

    The code for table one takes care of the NOT NULL with the Primary Key directive.

    CREATE TABLE Table1(

    id_tbl INT Primary Key,

    value1 Varchar(100)

    )

    When you use Primary Key with no other qualifiers in a create table statement several things happen.

    1. The column is immediately flagged as NOT NULL

    2. A Clustered Index is created on the column

    3. The column is the Primary key of the table.

    Been that way since SQL 7, when I started with SQL.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for the question. Cheers

  • I had to guess... but got it right. Thanks for a bit of excitment for my Friday! 😉

  • Had to think about that one for a sec. Nice question.

Viewing 15 posts - 16 through 30 (of 39 total)

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