May 18, 2012 at 6:07 am
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.
May 18, 2012 at 6:21 am
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
May 18, 2012 at 6:57 am
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. 🙂
May 18, 2012 at 7:10 am
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.
May 18, 2012 at 7:15 am
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
May 18, 2012 at 7:16 am
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.
May 18, 2012 at 7:23 am
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.
May 18, 2012 at 7:31 am
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
May 18, 2012 at 7:51 am
This was removed by the editor as SPAM
May 18, 2012 at 8:31 am
Good question, thanks.
May 18, 2012 at 8:49 am
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.
May 18, 2012 at 8:55 am
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
May 18, 2012 at 9:21 am
Thanks for the question. Cheers
May 18, 2012 at 9:32 am
I had to guess... but got it right. Thanks for a bit of excitment for my Friday! 😉
May 18, 2012 at 9:58 am
Had to think about that one for a sec. Nice question.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply