April 4, 2011 at 9:48 pm
Comments posted to this topic are about the item Foreign Keys
April 4, 2011 at 9:49 pm
Nice question, thanks!
April 4, 2011 at 10:22 pm
Great question. I actually use a question similar to this during interviews of candidates.
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
April 5, 2011 at 12:56 am
Learnt sumthing here::satisfied:
The code will work when using temp tables because "FOREIGN KEY constraints are not enforced on local or global temporary tables".
April 5, 2011 at 12:59 am
Funny - I take some things I know so for granted that I don't even consider it might not be as elementary for others.
In this case, it was the PRIMARY KEY/UNIQUE requirement for a FOREIGN KEY. I have not once considered that this might be the issue being tested in this question. I honestly thought: "okay, obviously the INSERT will fail, and I guess the author wants to test if we think that this aborts and rolls back the entire batch (thus causing the table not to be created), or only aborts and rolls back the offending INSERT statement".
It was only when I read the explanation that I realized that pointing a FOREIGN KEY to the PRIMARY KEY is so common that people might not know that poiting it to a UNIQUE constraint works as well.
Good question!
April 5, 2011 at 1:24 am
I couldn't work out why the create table statement would fail, so thought it was an easy question. It was only when I read the explanation that I realised it wasn't that easy after all. I completely missed the fact that the referenced column was unique rather than a primary key, and although I'd like to think I'd still have got the question right if I'd noticed, I guess we'll never know!
Thanks for the question!
April 5, 2011 at 2:18 am
Nice question, took some careful thought to get the correct answer.
April 5, 2011 at 2:53 am
Great question.
One thing that I'd add is that as the unique key will be created as a non-clustered index where as the default for a primary key is to create a clustered index. So in the example above you'd be left with a heap.
I personally find it best to be clear and explicitly state the type of index that is required (clustered or non-clustered) - but that's just me 🙂
good question though.
cheers.
April 5, 2011 at 3:17 am
The fact that the actual test was to demonstrate that a FOREIGN KEY does not necessarily have to reference a PRIMARY KEY, and can also reference a UNIQUE KEY - struck me only after reading the explanation!
I thought that it was quite common knowledge that the basics of any RDBMS is uniqueness because that's what establishes a relation (and why we need an intermediate tables to realize complex relationships). With this theory, the core requirement is that a foreign key can only be created against a unique key. Foregin keys can be created against primary keys because they are unique.
Anyway, it is really a good question, and thank-you for the same.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 5, 2011 at 3:20 am
martin catherall (4/5/2011)
Great question.One thing that I'd add is that as the unique key will be created as a non-clustered index where as the default for a primary key is to create a clustered index. So in the example above you'd be left with a heap.
I personally find it best to be clear and explicitly state the type of index that is required (clustered or non-clustered) - but that's just me 🙂
good question though.
cheers.
Very true and important point, Martin. Thank-you for bringing it up.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 5, 2011 at 3:36 am
martin catherall (4/5/2011)
Great question.One thing that I'd add is that as the unique key will be created as a non-clustered index where as the default for a primary key is to create a clustered index. So in the example above you'd be left with a heap.
Unless something has changed recently, this is not true.
The index created for a UNIQUE constraint will be clustered if there is no clustered index yet on the table, nonclustered otherwise. So in this case, I'd expect a clustered index.
I personally find it best to be clear and explicitly state the type of index that is required (clustered or non-clustered) - but that's just me 🙂
Nods in complete agreement. (And the agreement is not to "that's just me", but to "clearly and explicitly state the type of index")
April 5, 2011 at 4:06 am
Hugo Kornelis (4/5/2011)
The index created for a UNIQUE constraint will be clustered if there is no clustered index yet on the table, nonclustered otherwise.
Not true. See the 'CREATE TABLE' topics in BOL (http://msdn.microsoft.com/en-us/library/aa258255(v=sql.80).aspx, http://msdn.microsoft.com/en-us/library/ms174979.aspx):
CLUSTERED | NONCLUSTERED
Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.
UNIQUE Constraints
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
This is true for versions, at least, from 2000 to Denali.
April 5, 2011 at 6:53 am
Hugo Kornelis (4/5/2011)
Funny - I take some things I know so for granted that I don't even consider it might not be as elementary for others.In this case, it was the PRIMARY KEY/UNIQUE requirement for a FOREIGN KEY. I have not once considered that this might be the issue being tested in this question.
I think people are confused about this because of the way it is usually presented. To me, the requirement of a Foreign Key is that it identify a unique row in the target table, and that's the only requirement. Both Unique and Primary keys do that, because it is a requirement of the primary key that it be Unique. But all the books (and even BoL, albeit to a lesser extent than most books) insist on mentioning primary keys first, and mention Unique keys only as an afterthought after much discussion of primary keys or not at all, so naturally many of the people who learn from those books get the wrong impression.
And yes, it was a good question.
Tom
April 5, 2011 at 6:54 am
vk-kirov (4/5/2011)
Hugo Kornelis (4/5/2011)
The index created for a UNIQUE constraint will be clustered if there is no clustered index yet on the table, nonclustered otherwise.Not true. See the 'CREATE TABLE' topics in BOL (http://msdn.microsoft.com/en-us/library/aa258255(v=sql.80).aspx, http://msdn.microsoft.com/en-us/library/ms174979.aspx):
CLUSTERED | NONCLUSTERED
Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.
UNIQUE Constraints
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
This is true for versions, at least, from 2000 to Denali.
You are absolutely right. I was confused the default for PRIMARY KEY (clustered or nonclustered, depending on other indexes on the table) and UNIQUE (always nonclustered).
Apologies for the confusion.
April 5, 2011 at 7:17 am
I got this right, but the answer should be reworded that the LAST INSERT will fail. The first one works fine.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply