Foreign key

Question

Foreign key

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hardy21 (5/17/2012)
Good One.
If Table2 declared as following than all 3 tables created without any issue.
create table Table2
( id_tb2 int primary key references table1(id_tb1),
value2 varchar(100))

Yes, sure

That is the point of the question.

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

EL Jerry SSCertifiable Points: 7063 More actions · Answer 2

Great question. I had not worked with REFERENCES before, but learned something new today.

Have a great weekend everyone!!

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Ken SSCoach Points: 17191 More actions · Answer 3

Nice question and good discussion. Learned something new today!

Paul White SSC Guru Points: 150467 More actions · Answer 4

bitbucket-25253 (5/17/2012)
Well my answer was declared to be incorrect, so went to read the explanation, following the link given ..... nada / nothing / not a word to support what is deemed the correct answer.

The link explains that the target of the foreign key reference must have a PRIMARY or UNIQUE constraint. What do you think is missing or incorrect?

So went and entered the code in SQL 2008R2, triple checked what I had entered and it matched what was given in the question. When pressing the F5 key for SSMS (2008R2) .. and again using SSMS checked after the error message and what do you know ... what was shown did NOT match what was give as the correct answer.

Can you share the code you ran and the output you saw? I see the output I expected on 2005, 2008, 2008 R2, and 2012; two tables are created, and an error message of the type shown is returned for the third.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

mtassin (5/18/2012)
When you use Primary Key with no other qualifiers in a create table statement several things happen.
1. The column is immediately flagged as NOT NULL
2. A Clustered Index is created on the column
3. The column is the Primary key of the table.
Been that way since SQL 7, when I started with SQL.

Just a comment regarding point 2:

CREATE TABLE dbo.Example (id integer PRIMARY KEY, ak integer NULL UNIQUE CLUSTERED);

Also, I prefer not to rely on defaults - it costs very little to be explicit. My choice is to always specify NULL/NOT NULL and CLUSTERED/NONCLUSTERED.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 7

Nice question - confused me at first until I realised no key was being created in table 2.

This type of question really shows that's it worth reading something carefully!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

zymos SSCommitted Points: 1960 More actions · Answer 8

Interesting concept, but the answer(s) options should have been more specific as to what actually tables are expected to be created. Otherwise, great explanation.

Thanks.

Neha05 Default port Points: 1494 More actions · Answer 9

Neha05

Default port

Points: 1494

August 9, 2012 at 9:04 am

#1523865

Nice question.