April 6, 2012 at 8:41 am
I was thinking this was a tricky question due to the ambiguity. Good to see others felt the same way.
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 6, 2012 at 8:55 am
Jack Corbett (4/6/2012)
Sean Lange (4/6/2012)
Hugo Kornelis (4/6/2012)
Sean Lange (4/6/2012)
Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key. In that case it is more of a business rule and can be sidestepped by disabling the trigger temporarily. The same cannot be said for a foreign key constraint. You can't just disable the constraint and turn it back on with bad data like you could with a trigger style implementation.Well, actually you can do just that:
Hugo I had no idea that would work. I assumed a check constraint would cause issues if you disabled it and put in bad data, then re-enabled it. Thanks for the lesson and excellent example.
There's actually a bug around this as well, if done within a transaction, https://connect.microsoft.com/SQLServer/feedback/details/685800/parameterized-delete-and-merge-allow-foreign-key-constraint-violations
I was going to post basically what Hugo did, but he beat me to it!
Wow I would never have thought about that scenario causing that type of issue. I would have assumed that would not happen. Good to know!!!
_______________________________________________________________
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 6, 2012 at 10:18 am
Thanks Hugo, for providing the example. That helped me see why I got it wrong!
Happy Easter!
April 6, 2012 at 10:53 am
I'll add my voice to those saying this was a rather poorly worded, ambiguous question.
I took the third option to mean, 'Technically no, but you can mostly simulate foreign key behavior by using a trigger", which is true.
So, like Hugo, I flipped a coin. Sadly, I came up UN-lucky.
Rob Schripsema
Propack, Inc.
April 6, 2012 at 11:38 am
Sean Lange (4/6/2012)
... Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key.
NO.
Any column in a table is a foreign key column if it references a column in another table. It is a conceptual property of the column.
Whether you enforce the integrity of that reference via a built-in SQL Server constraint or custom triggers is irrelevant to the question of whether a foreign key relationship exists. (In fact, it could be argued that if you choose not to enforce referential integrity at all, but still use that column as a reference to another column in another table, it is still a foreign key.)
So the corrrect, complete answer is 3 (Yes, with triggers). The correct, incomplete answer is 1 (Yes). And 2 (No) is incorrect.
April 6, 2012 at 12:11 pm
sknox (4/6/2012)
Sean Lange (4/6/2012)
... Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key.NO.
Any column in a table is a foreign key column if it references a column in another table. It is a conceptual property of the column.
Whether you enforce the integrity of that reference via a built-in SQL Server constraint or custom triggers is irrelevant to the question of whether a foreign key relationship exists. (In fact, it could be argued that if you choose not to enforce referential integrity at all, but still use that column as a reference to another column in another table, it is still a foreign key.)
So the corrrect, complete answer is 3 (Yes, with triggers). The correct, incomplete answer is 1 (Yes). And 2 (No) is incorrect.
I disagree completely. A logical type of key you are describing cannot be a foreign key. The definition of a foreign key indicates that RI must be enforced. If RI is not enforced it is NOT a foreign key.
As you said at first it must reference a column in another table. That directly contradicts your statement about RI.
In reference to SQL Server the trigger idea is not a key. It is a logical way to enforce RI. A foreign key is an object and has a row in sys.sysobject with an xtype = 'F'.
_______________________________________________________________
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 6, 2012 at 12:30 pm
tks for the question.
Many valid points thrown out today as always. For me it boils down to whether or not the intent of the question was to talk about the FOREIGN KEY constraints or trigger based referential integrity. I don't see these as the same thing, so ambiguity FTW! 😛
Bring on the weekend and Happy Easter everyone!
April 6, 2012 at 1:26 pm
Rather a lot of complaints about ambiguity/trickiness so far. I think they are partially justified, but really there isn't a way to make either of the incorrect options fit the question as well as the correct option does. Certainly the wording "declare a foreign key constraint" would have been better than "create a foreign key", but I don't think there's any real ambiguity. Of course everyone has become quite used to "trick questions" in QoTD, and my initial reaction was something like "is this a braindead trick question so that the triggers answer will get the point?" but the logical thing to do is to assume no trick, and then it's easy to get it right.
Bear in mind that it has been said several times (because people have complained that questions don't specify "sql 2008 or later" even though there is no other supported release of sql server) that unless questions specify what system they apply to they apply to a release of SQL Server that is currently supported by Microsoft, and this question doesn't specify any system; that means that the argument "Oracle does it with triggers" advanced by one commenter falls by the wayside, as do "some DBMSs used to enforce them with triggers", because neither Oracle nor any of those "some databases" is a currently supported release of SQL Server.
Although, as Jaroslav Pfauser pointed out, the wording of the answer options combined with the use of radio buttons so that only one answer could be correct logically precludes the "yes with triggers" option anyway I would think of a question that needed that reasoning to answer it as a poor question. But that isn't needed at all.
While it's true that referential integrity between tables in different databases can be enforced using triggers, the question wasn't about how referential integrity can be enforced but about how a "foreign key" could be created - and it was about a currently supported issue of SQL server so "foreign key" must be interpreted as something in SQL server which (regrettably) dosn't match up precisely to the relational model, so we have to work out what "create a foreign key" means in an SQL Server database context. There are two approaches to this that seem reasonable: one to guess that the wording is sloppy and "foreign key" should have been "foreign key constraint"; the other to guess that it means "make a column list the subject of a foreign key constraint"; with either approach, we end up needing the thing which currently supported versions of SQL server call a foreign key constraint, and that thing specifically does not support cross-database cross-database references, and that delivers the correct answer.
Tom
April 6, 2012 at 2:31 pm
L' Eomot Inversé (4/6/2012)
people have complained that questions don't specify "sql 2008 or later" even though there is no other supported release of sql server
Officially, SQL Server 2008R2 is a different release than SQL Server 2008. (Yeah, nitpicking, I know - though people in the BI field probably think differently and do see a lot of differences between the two).
More important: SQL Server 2012 has been released to manifuctaring a month or so, has been available in some editions through MSDN for a few weeks, and was generally available in all editions since April 1st or 2nd or so.
April 7, 2012 at 8:42 am
Hugo Kornelis (4/6/2012)
L' Eomot Inversé (4/6/2012)
people have complained that questions don't specify "sql 2008 or later" even though there is no other supported release of sql serverOfficially, SQL Server 2008R2 is a different release than SQL Server 2008. (Yeah, nitpicking, I know - though people in the BI field probably think differently and do see a lot of differences between the two).
More important: SQL Server 2012 has been released to manifuctaring a month or so, has been available in some editions through MSDN for a few weeks, and was generally available in all editions since April 1st or 2nd or so.
Yes, quite so: those are the three releases currently supported. "SQL 2008 or later" clearly includes SQL 2008 R2 and SQL 2012 and there is no other supported release than these three (perhaps you are suggesting that either 2008 R2 or2012 is not later than 2008?).
Tom
April 7, 2012 at 3:23 pm
L' Eomot Inversé (4/7/2012)
Hugo Kornelis (4/6/2012)
L' Eomot Inversé (4/6/2012)
people have complained that questions don't specify "sql 2008 or later" even though there is no other supported release of sql serverOfficially, SQL Server 2008R2 is a different release than SQL Server 2008. (Yeah, nitpicking, I know - though people in the BI field probably think differently and do see a lot of differences between the two).
More important: SQL Server 2012 has been released to manifuctaring a month or so, has been available in some editions through MSDN for a few weeks, and was generally available in all editions since April 1st or 2nd or so.
Yes, quite so: those are the three releases currently supported. "SQL 2008 or later" clearly includes SQL 2008 R2 and SQL 2012 and there is no other supported release than these three (perhaps you are suggesting that either 2008 R2 or2012 is not later than 2008?).
Sorry, my bad. I somehow managed to overlook the "or later" in your message.
April 7, 2012 at 11:32 pm
Thanks for the question.
I think what the author meant was that a FK cannot reference an external database and the explanation says there is a way to SIMULATE a FK through triggers. At least, that's what I understood and I saw no ambiguity in the answers. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
April 8, 2012 at 11:25 am
Thought it was a trick question. Learned something today. Thanks for submitting.
http://brittcluff.blogspot.com/
April 9, 2012 at 6:57 am
My coin flips always seem to turn out the wrong way. *sigh*
April 9, 2012 at 7:21 am
Sean Lange (4/6/2012)
sknox (4/6/2012)
Sean Lange (4/6/2012)
... Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key.NO.
Any column in a table is a foreign key column if it references a column in another table. It is a conceptual property of the column.
I disagree completely. A logical type of key you are describing cannot be a foreign key. The definition of a foreign key indicates that RI must be enforced. If RI is not enforced it is NOT a foreign key.
1. If RI enforcement is a requirement for a foreign key, then why can you in SQL server create a foreign key that does not enforce referential integrity (e.g, ON DELETE NO ACTION ON UPDATE NO ACTION) ?
As you said at first it must reference a column in another table. That directly contradicts your statement about RI.
2. Referencing a column in another table does not imply enforcement of referential integrity. The key is in the term: "referential integrity" means the integrity of the reference. It is possible to have a reference that does not have its integrity enforced. That does not negate the fact that it is a reference.
In reference to SQL Server the trigger idea is not a key. It is a logical way to enforce RI. A foreign key is an object and has a row in sys.sysobject with an xtype = 'F'.
3. I agree that the trigger is not a key, but a way to enforce RI. A Foreign Key object in SQL server is not a key either. It is a logical way to document a key that exists and potentially to enforce RI on that key.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply