July 1, 2015 at 7:48 am
+1
July 1, 2015 at 8:05 am
Xavon (7/1/2015)
matthew.flower (7/1/2015)
I agree completely with Toreador on this - the query returns the two table names and the name of the foreign key constraint between them.As the first two answers seem to mean the same thing it came down to the fine details:
"The foreign key name [that] exists between the source and destination tables."
The name of the foreign key constraint is what the query returns - so this was my answer.
"Shows the relationship that exists in each table and the foreign key name used in the relationship"
No the query doesn't return the relationship. it shows that they are connected, but not how which to me requires knowledge of which columns relate to each other and perhaps the on-delete and on-update actions. So to me this answer is wrong.
But that is not what the official answer is - am I misinterpreting the meaning of the words?
+1
I picked one because there is no details of the FK relationship (other than name) which the second response seems to be asking for.
Exactly - there are no details. It doesn't return the relationship. I don't think there's any misinterpretation here - the answers are worded poorly. The "correct" answer is wrong, but it isn't the first time that a poor QOTD has sparked a debate.
July 1, 2015 at 8:20 am
This was removed by the editor as SPAM
July 1, 2015 at 8:24 am
Stewart "Arturius" Campbell (7/1/2015)
Ed Wagner (7/1/2015)
it isn't the first time that a poor QOTD has sparked a debate.I have often learned more from the debate than the question itself
Agreed. When you get a bunch of people with a depth of knowledge like those on this site and they start debating something, it sometimes goes in many different directions. Things get brought up that I wouldn't typically consider. The discussion (as opposed to a simple, one-line answer) and multiple approaches to a problem are the things I enjoy most about this site.
July 1, 2015 at 9:28 am
Well, the query returned the two table names and the name of the foreign key.
What it didn;t do was show the relationship (i.e. columns) between the two.
To me, that makes A the correct answer.
July 1, 2015 at 10:17 am
Richard Warr (7/1/2015)
Well, the query returned the two table names and the name of the foreign key.What it didn;t do was show the relationship (i.e. columns) between the two.
To me, that makes A the correct answer.
+1 I agree.
Don Simpson
July 1, 2015 at 10:27 am
come on people, stop being sore losers ;p
useful bit of code in the question, i agree the answer choices were a bit confusing, but at least it gives me a code snippet i can run if i'm looking for foreign keys on a table...
July 1, 2015 at 10:36 am
This was removed by the editor as SPAM
July 1, 2015 at 10:48 am
Thanks Junior, interesting, but as you can see from the discussion, a bit piquant question.;-)
English is not my native language. So I had translated answer No. 1 that the result
of the query is a list of items with the relation 1 : 1 - but the (ONLY ONE) foreign key name exists...,
not the foreign key NAMES exists... - Answer No. 2 that the result of the query is a list of items
with the relations n : n. With answer No. 2 I have got it right. That's all.
July 1, 2015 at 11:15 am
Hi Ed,
Thanks for comment, I agree with you.
July 1, 2015 at 11:26 am
I understand the people who claim that the first two answer options are the same, but I do not agree with them.
I also disagree with the "correct" answer.
The query returns three columns: source table, reference table, and FK name. The content of the query matches the column names, so no nastiness over there. So what we get are the foreign key name, source table, and reference (destination) table. Nothing else.
Also - relationships do not exist in a relational database. Relationships exist in a relational design. When implementing the design, relationships are implemented with foreign key constraints. So a foreign key is the implementation of a relationship, just like a table is the implementation of an entity type, a column is the implementation of an attribute, and a check constraint is (sort of) the implementation of a domain.
No system tables in SQL Server can return any information about attributes, domains, or relationships, because they are elements of the design phase, not of the implementation in a database such as SQL Server.
July 1, 2015 at 12:11 pm
Hugo Kornelis (7/1/2015)
Also - relationships do not exist in a relational database. Relationships exist in a relational design.
+1
Don Simpson
July 1, 2015 at 7:26 pm
All tree answers are unrelated to the question of your exercise, i.e.: you are asking quote "What does this code return? " unquote and in all of the three answers you are talking about foreign key relations etc...The straight answer to your question is that your code returns nothing (empty data set) which has nothing to do with your three answers. Next time please think morefully before asking a question moreover constructing the choices of the answers.
July 1, 2015 at 8:27 pm
I much prefer clearer language.
Also, I really prefer to see scripts like this updated to not use those old objects and views.
/* deprecated use of sysforeignkeys */
SELECT so.name AS 'Source Table'
, OBJECT_NAME(rkeyid) 'Reference Table'
, OBJECT_NAME(constid) AS 'FK Name'
FROM sys.objects so
INNER JOIN sysforeignkeys sf
ON so.object_id = sf.fkeyid
ORDER BY so.name;
/* should be */
SELECT so.name AS 'Source Table'
, OBJECT_NAME(sf.referenced_object_id) 'Reference Table'
, sf.name AS 'FK Name'
FROM sys.objects so
INNER JOIN sys.foreign_keys sf
ON so.object_id = sf.parent_object_id
ORDER BY so.name;
/* and could be simpler */
SELECT OBJECT_NAME(sf.parent_object_id) AS 'Source Table'
, OBJECT_NAME(sf.referenced_object_id) 'Reference Table'
, sf.name AS 'FK Name'
FROM sys.foreign_keys sf
ORDER BY 'Source Table';
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
July 2, 2015 at 12:52 am
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 37 total)
You must be logged in to reply to this topic. Login to reply