April 5, 2011 at 7:34 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 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!
Exactly the same here. I was trying to make sure about batches more than the actual foreign key on the UNIQUE constraint.
April 5, 2011 at 8:18 am
mtassin (4/5/2011)
I got this right, but the answer should be reworded that the LAST INSERT will fail. The first one works fine.
Mark:
Good point. Thanks for bringing it up.
April 5, 2011 at 8:31 am
Interesting question..
April 5, 2011 at 9:00 am
Peter Maloof (4/5/2011)
mtassin (4/5/2011)
I got this right, but the answer should be reworded that the LAST INSERT will fail. The first one works fine.Mark:
Good point. Thanks for bringing it up.
Yes, I noticed the same thing. There are two INSERT statements in the code.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 5, 2011 at 9:55 am
I didn't even really read the create table statements. I saw the insert at the bottom with a StudentID, skimmed up and saw it referenced the students table and that the insert above it did not have a 4. I totally missed the point of foreign key referencing a Unieq column. Nice straight forward question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 5, 2011 at 11:09 am
Thanks for the question
M&M
April 5, 2011 at 12:53 pm
Sean Lange (4/5/2011)
I didn't even really read the create table statements. I saw the insert at the bottom with a StudentID, skimmed up and saw it referenced the students table and that the insert above it did not have a 4. I totally missed the point of foreign key referencing a Unieq column. Nice straight forward question.
I did the same thing.
I also agree with the previous comment that the answer should be reworded to specify the second INSERT statement fails. While I got this question correct (I figured it was referring to the second INSERT statement), I can see how it might create some confusion. But then, none of the other questions was correct, so that also made it easier to answer. 🙂
April 5, 2011 at 11:14 pm
Nice Question. I would like to add some more point. The primary requirement for foreign key constraint is uniquenes in the referenced table. This uniqueness is acheived by creating unique index (clustered or non clustered) in 3 ways.
1. Define primary key (by default create unique clustered index, if already this exists then creates unique non clustered index)
2. Define unique constraint (by default create nonclustered, unique)
3. Without having primary key or unique key just create unique clustered or non clustered index.
Below code still gives the same result without defining primary or unique key.
So it is not mandatory to define primary or unique key to have foreign key relation.
CREATE TABLE dbo.Students (
StudentID INT, -- UNIQUE,
LastName VARCHAR(20),
FirstName VARCHAR(20)
)
CREATE UNIQUE CLUSTERED INDEX IDX_1 ON Students(StudentID)
--====> OR CREATE UNIQUE NONCLUSTERED INDEX IDX_1 ON Students(StudentID)
INSERT INTO dbo.Students
VALUES(1,'Washington','George'),
(2,'Adams','John'),
(3,'Jefferson','Thomas')
CREATE TABLE dbo.ClassList (
ClassID VARCHAR(6),
StudentID INT REFERENCES dbo.Students(StudentID)
)
INSERT INTO dbo.ClassList VALUES('HIS101',4)
April 5, 2011 at 11:34 pm
Gopinath Srirangan (4/5/2011)
......3. Without having primary key or unique key just create unique clustered or non clustered index.
Below code still gives the same result without defining primary or unique key.
So it is not mandatory to define primary or unique key to have foreign key relation.
......
Per Books On Line (http://msdn.microsoft.com/en-us/library/ms190639.aspx),
If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
Hence, even if you do not choose to define a clustered index on a UNIQUE column, the Database Engine creates one for you.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 6, 2011 at 3:00 am
Nakul Vachhrajani (4/5/2011)
Gopinath Srirangan (4/5/2011)
......3. Without having primary key or unique key just create unique clustered or non clustered index.
Below code still gives the same result without defining primary or unique key.
So it is not mandatory to define primary or unique key to have foreign key relation.
......
Per Books On Line (http://msdn.microsoft.com/en-us/library/ms190639.aspx),
If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
Hence, even if you do not choose to define a clustered index on a UNIQUE column, the Database Engine creates one for you.
True (for clustered indexes only!), but not really relevant for the question. The uniqueifier is purely internally; for all external purposes, the index is not unique. If you create a nonunique clustered index, you cannot use it to reference the table for a FOREIGN KEY constraint.
April 6, 2011 at 3:17 am
Yes. I mean to say the same that we cannot achieve FOREIGN KEY relation just by creating nonunique clustered or nonunique nonclustered index.
And its TRUE even by creating unique nonclustered index.
April 6, 2011 at 9:34 am
StudentID is nullable and so the Students table does not have a key at all (candidate keys do not permit nulls). Therefore the StudentID column in ClassList is actually not a foreign key - because it doesn't reference a candidate key.
The phrase "unique key" was used in this thread. It's a term that always bothers me for two reasons. Firstly because it seems to be a tautology - keys are by definition unique so why not just say "key" instead of "unique key". Secondly, the phrase "unique key" seems to be used as though it is synonymous with UNIQUE constraints in SQL. UNIQUE constraints are one way of implementing keys but it doesn't follow that the set of columns in a unique constraint always make up a key.
According to the SQL standard, rows with nulls in a unique constraint don't have to be unique at all (although SQL Server disagrees and requires them to be unique anyway). Columns subject to UNIQUE constraints can contain nulls. Columns subject to UNIQUE constraints may be superkeys but they don't have to be minimal superkeys. So that's three reasons why a UNIQUE constraint doesn't necessarily imply that its columns are a candidate key - as is the case with this example.
April 6, 2011 at 9:36 am
Nakul Vachhrajani (4/5/2011)
Per Books On Line (http://msdn.microsoft.com/en-us/library/ms190639.aspx),If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
Hence, even if you do not choose to define a clustered index on a UNIQUE column, the Database Engine creates one for you.
No, read that section again. If you create a clustered index that is not unique the Database Engine will add a uniqueifier column to the clustered index key. It has nothing to do with creating a UNIQUE column. (Which would get a non-clustered index.)
April 7, 2011 at 1:24 pm
David Portas (4/6/2011)
StudentID is nullable and so the Students table does not have a key at all (candidate keys do not permit nulls). Therefore the StudentID column in ClassList is actually not a foreign key - because it doesn't reference a candidate key.The phrase "unique key" was used in this thread. It's a term that always bothers me for two reasons. Firstly because it seems to be a tautology - keys are by definition unique so why not just say "key" instead of "unique key". Secondly, the phrase "unique key" seems to be used as though it is synonymous with UNIQUE constraints in SQL. UNIQUE constraints are one way of implementing keys but it doesn't follow that the set of columns in a unique constraint always make up a key.
According to the SQL standard, rows with nulls in a unique constraint don't have to be unique at all (although SQL Server disagrees and requires them to be unique anyway). Columns subject to UNIQUE constraints can contain nulls. Columns subject to UNIQUE constraints may be superkeys but they don't have to be minimal superkeys. So that's three reasons why a UNIQUE constraint doesn't necessarily imply that its columns are a candidate key - as is the case with this example.
Yes, it's a tautology because being a key already implies UNIQUE.
And the lunacy of the SQL standard renders the use of a UNIQUE column (or column set) as the target of a foreign key relationship somewhat impossible. The SQL Server lesser stupidity of allowing a single NULL also creates problems - generally a NULL in the source of a foreign key relationship indicates that the target is unknown, but if the target column allows NULL we may want to point at that.
I regard anyone who allows NULL in unique columns as crazy (presumably David you agree with me, as you think anyone who allows NULL anywhere is crazy). I wonder if other people have seen Hugo's connect proposal to change SQL Server for the worse so that it conforms to the SQL standard on this - there's a surprisingly (and worryingly) small number of votes against it.
Tom
April 10, 2011 at 11:45 pm
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply