November 30, 2012 at 6:45 am
I got this question right after about 5 seconds or less of thought. My reasoning was that if you have to set something (like NULL Foreign keys or cascading deletes ...) and the simple 1 sentence question did not specify that those settings were in fact set then obviously they were not set.
Ok, sometimes things in the QotD aren't obvious when I think they are. Maybe I got lucky this time.;-)
Enjoy!
November 30, 2012 at 6:55 am
I actually got this one right (according the the marked answer) it's one of the few I do get right before I research the answer.
For those talking about ON DELETE CASCADE. The assumption was made that the choice included the delete from the secondary table. On face value the 3 other options needed a pretty big leap in logic (e.g. NULLs on the FK) from the text in order to make them false. The option about the Delete is the only one, in my mind, where there was any ambiguity. The ambiguity comes from do you assume that the secondary table also has its rows deleted through a CASCADE or not. Since item 5 was whole wrong on its face and you have 3 items with very little ambiguity, the Delete item must have assumed that the rows in the secondary table would not be deleted.
November 30, 2012 at 6:57 am
arthurolcot (11/30/2012)
Lokesh Vij (11/30/2012)
Stewart "Arturius" Campbell (11/30/2012)
Koen Verbeeck (11/30/2012)
Got it wrong because you can update or delete rows in the primary tables if you specify ON CASCADE options.Next time do more homework please.
+1
+1
I think most of us have got this wrong due to this statement 🙂
+1
Yep, i've just done exactly the same..
+1 Thanks for the question and great discussion. Seems as though I wasn't alone in my thought process.
November 30, 2012 at 7:02 am
dineshbabus (11/30/2012)
ok guyz lets come to a conclusion. Whether question is not clear or we ourselves getting confused?
I have to say the question and answer options provided were not clear, in that there was at least one answer you could pick that was deemed "correct" but was in fact an "incorrect" answer (being able to delete PK when FK exists, with CASCADE), thus making it a viable option to this question. Also, double negatives are a bad way of framing questions. All they do is induce confusion.
Hakim Ali
www.sqlzen.com
November 30, 2012 at 7:13 am
I got this question correct because I was thinking of my own preferences. I do not like allowing NULL values or turning on CASCADING....so it made sense not to consider those options.
I do agree that some additional DDL statements would have erased the ambiguity. Nice discussion though.
Aigle de Guerre!
November 30, 2012 at 7:50 am
This is a horrible question, I see only one correct answer in the 5 answers.
There is definitely not enough information in the question to give a reasonable answer.
Thomas
November 30, 2012 at 8:46 am
What a terrible question and horribly wrong answer.
Actually, if it had said it was about SQL Server 6.5 it might have been alright; I don't think it it would have been, but it's possible; I don't know enough about SQL 6.5 to rule it out. It's certainly utterly wrong for anything from SQL 2000 onwards.
I think it's also wrong for SQL 7.0 but I can't find 7.0 BoL so not 100% sure.
First of all, on what the right answer is claimed to be:-
1) Can delete a row from primary table if there are related rows in secondary table is certainly true, not false, because I can use "ON DELETE CASCADE" in the definition of the relationship.
2) Can insert a new row in secondary table if there are not related rows in primary table is also clearly true, not false, because I can insert a row with NULL in the relevant column into the secondary table (unless there's an unmentioned NOT NULL requirement).
So both "correct" answers are actually incorrect.
Then, are the other options true or false? According to the answer given, they are all true.
3) Can’t update secondary table’s foreign key if there is no related row in primary table. That's false, not true, as I can change the foreign key in a row in the secondary table to NULL (unless there's an unmentioned NOT NULL requirement).
4) Breaking of relationships is prevented once referential integrity on a database is enforced. Here the plural relationships seems to suggest that we are talking about relationships between rows; I can trivial break such a relationship by setting the reference in the referring row to NULL (unless there's an unmentioned NOT NULL requirement), or by changing it to point to a different row, so if that is what is meant this option is false not true. But "on a database" perhaps suggests that we are talking about relationships between tables, not between rows, and since I can't break on of those without deleting the referential integrity constraint involved that interpretation means this option is true.
5) Can’t update primary table’s primary key if row being modified has related rows in secondary table. That too is false, not true, for at least two good reasons: the foreign key relationship may refer to some candidate key in the primary table that is not its primary key; and/or I can include ON UPDATE CASCADE in the definition of the relationship.
So two of the three "incorrect" options are actually correct, and maybe all three are actually correct (depending on the interpretation of one of them).
The question is bad as well as the answer. It offers a badly worded (ambiguous meaning) option. It talks about a "secondary table" instead of a "referring table", and a "primary table" instead of a "target table" or a "table referred to" - we are talking about referential integrity, not something like "ordinal integrity", aren't we?
The reference given in the "explanation" is to a description of a rather old version of SQL Server - not sure whether it's 7.0 or 6.5, as it's dated October 1998 it ought to be 7.0 but some of its content seems to be not quite that up to date. It contains statements like "cascading update is not supported by SQL Server" and "The declarative RI provided by Microsoft SQL Server enforces only restricted deletes when a primary key column is updated", which have been false of SQL Server from SQL 2000 onwards, and is riddled with the assumptions that the reference must be to a single column and that the reference must be to a primary key, both of which have also been false in every release since SQL 2000 (and, I believe, were already false in SQL Server 7, since, unlike the cascade update and delete capability, these features are not mentioned on the Relational Database Enhancements BoL page for SQL 2000.
Tom
November 30, 2012 at 8:57 am
Dave62 (11/30/2012)
I got this question right after about 5 seconds or less of thought. My reasoning was that if you have to set something (like NULL Foreign keys or cascading deletes ...) and the simple 1 sentence question did not specify that those settings were in fact set then obviously they were not set.
I can't agree. The default is that NULL is allowed, not forbidden, and that's been true in every release of SQL Server since at least 7.0 (and I imagine it's been true at least since release 4.2). So for the answers to be right you would have to assume that someone specified a setting to override the default.
Nor can you claim that by default the candidate key choisen as the target for a foreign key relationship is the primary key, since there is no default - the key has to be explicitly specified.
What seems to have happened here is that someone read a (rather poor, as Hugo pointed out) note about SQL 7.0 (or perhaps SQL 6.5) and based a question about currently supported versions of SQL Server on that reading, with predictably disastrous results.
Tom
November 30, 2012 at 9:05 am
Dana Medley (11/30/2012)
arthurolcot (11/30/2012)
Lokesh Vij (11/30/2012)
Stewart "Arturius" Campbell (11/30/2012)
Koen Verbeeck (11/30/2012)
Got it wrong because you can update or delete rows in the primary tables if you specify ON CASCADE options.Next time do more homework please.
+1
+1
I think most of us have got this wrong due to this statement 🙂
+1
Yep, i've just done exactly the same..
+1 Thanks for the question and great discussion. Seems as though I wasn't alone in my thought process.
+1 My instinct was that the ON DELETE has a cascade option, that makes the delete statement then TRUE. I agree, if the author wanted to either give a code base or specify using SQL defaults only then my answer would've changed.
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
November 30, 2012 at 9:09 am
L' Eomot Inversé (11/30/2012)
The reference given in the "explanation" is to a description of a rather old version of SQL Server - not sure whether it's 7.0 or 6.5, as it's dated October 1998 it ought to be 7.0 but some of its content seems to be not quite that up to date. It contains statements like "cascading update is not supported by SQL Server" and "The declarative RI provided by Microsoft SQL Server enforces only restricted deletes when a primary key column is updated", which have been false of SQL Server from SQL 2000 onwards, and is riddled with the assumptions that the reference must be to a single column and that the reference must be to a primary key, both of which have also been false in every release since SQL 2000 (and, I believe, were already false in SQL Server 7, since, unlike the cascade update and delete capability, these features are not mentioned on the Relational Database Enhancements BoL page for SQL 2000.
Tom, I also have been trying to find when cascading referential integrity was introduced. Like you, I could not find anything conclusive, but I might very well have been SQL 2000.
I am almost 100% sure that all versions of SQL Server that supported foreign key constraints allowed them on any columns (or set of columns) that was the target of a unique index - so the column(s) could be declared as PRIMARY KEY or UNIQUE, or they could even be neither, but subject to a seperate UNIQUE INDEX.
November 30, 2012 at 9:12 am
L' Eomot Inversé (11/30/2012)
Nor can you claim that by default the candidate key choisen as the target for a foreign key relationship is the primary key, since there is no default - the key has to be explicitly specified.
Actually, there is a default, and it is the primary key.
use tempdb;
create table t1 (a int primary key, b int unique);
create table t2 (c int references t1);
go
insert t1 values (1,2);
go
insert t2 values(1);
go
insert t2 values(2);
go
select * from t2;
go
drop table t2, t1;
go
November 30, 2012 at 9:22 am
Hugo Kornelis (11/30/2012)
Tom, I also have been trying to find when cascading referential integrity was introduced. Like you, I could not find anything conclusive, but I might very well have been SQL 2000.
Based on the article I referenced in my earlier reply, it looks like all the current options like Cascade Delete, were added in 2000. The article shows that Itzik Ben-Gan and Thomas Moreau are the authors. Quoting from the article,
"NO ACTION (restrict)
This type of action was the only one supported by the FOREIGN KEY constraint up until, and including, SQL Server version 7.0. It always enforces all of the following integrity rules: "
I'm not going to list the bullet points, but if one uses them as a basis for answering the question, it would lead one to select the posted "correct" answers for the question. The article is in the Technical Articles section for SQL Server 2000.
November 30, 2012 at 9:50 am
Hugo Kornelis (11/30/2012)[hrTom, I also have been trying to find when cascading referential integrity was introduced. Like you, I could not find anything conclusive, but I might very well have been SQL 2000.
That one was definitely SQL 2000 - see the page I referenced, which lists cascading RI as an enhancement in SQL 2000.
I suspect I should ramble less - I obviously make the point about cascading RI being spefically introduced in SQL 2000 and documented as such as clearly as I meant to - probably drowned it amonst too many words.
100% sure that all versions of SQL Server that supported foreign key constraints allowed them on any columns (or set of columns) that was the target of a unique index - so the column(s) could be declared as PRIMARY KEY or UNIQUE, or they could even be neither, but subject to a seperate UNIQUE INDEX.
This was in SQL 2000, but not listed as an enhancement in SQL 2000, so was presumably in SQL 7. Possibly earlier and don't know when RI constraints were introduced into SQL Server, but would be surprised if it were as late as 7.0.
According to SQL 2000's BoL, the target has to be a candidate key, which if true means that every column in the the target of a referential constraint has to be NOT NULL as well as there being a unique constraint/index on them. Of course that doesn't mean that the source columns have to be NOT NULL.
Tom
November 30, 2012 at 10:00 am
Hugo Kornelis (11/30/2012)
L' Eomot Inversé (11/30/2012)
Nor can you claim that by default the candidate key choisen as the target for a foreign key relationship is the primary key, since there is no default - the key has to be explicitly specified.Actually, there is a default, and it is the primary key.
You are right: SQL 2000 BoL says
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
I should have remembered that; but I think I forgot it well over a decade ago.
Tom
November 30, 2012 at 10:15 am
Next time, I will read every word in the question. Even though it's only 3 letters, "not" is a very important word!:blush:
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply