Create Table

Question

Create Table

Viewing 15 posts - 16 through 29 (of 29 total)

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

Dana Medley SSCertifiable Points: 6764 More actions · Answer 1

mtassin (4/26/2013)
Hugo Kornelis (4/26/2013)
I first focused on the reference to a unique constraint rather than the primary key, thinking that was the issue of the question. I almost got it wrong because of that, as I had first not given enough attention to the other details of the code.
I guess that many people will get this question right for the wrong reasons. (Many people think that a foreign key should always reference the primary key).
nah, that one I had... I've had to do enough FK's to Unique constraints in my life to remember that lesson...
I'm just glad that I re-read it... and noticed the different column sizes.

+1 thanks for the great question.

Everything is awesome!

Thorpehall Mr or Mrs. 500 Points: 560 More actions · Answer 2

kapil_kk (4/26/2013)
Mick Gillatt (4/26/2013)
... I also didn't find that information...

Thanks Kapil. I wasn't criticising your explanation of the answer, I only asked my question because I couldn't find the info where anywhere in BOL and that's frustrating. I suppose most people would think it natural that both columns should have the same length, although I was wondering what would be so bad about a foreign key column that is defined as a varchar type with a longer length than the column it references.

webrunner SSC-Dedicated Points: 31862 More actions · Answer 3

Nice question. Thanks.

-------------------
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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

thanks for the question

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

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Revenant

SSC-Forever

Points: 42467

April 26, 2013 at 10:23 am

#1610455

Thanks for an easy one, Kapil!

Miles Neale SSChampion Points: 13147 More actions · Answer 6

Nice one for a Friday.

Thanks!

Not all gray hairs are Dinosaurs!

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 7

Thanks for nice question.

---Statement 1

CREATE TABLE #table1

(

ID int PRIMARY KEY,

Name varchar(20)

CONSTRAINT UN_Name UNIQUE(Name)

);

CREATE TABLE #table2

(

ID int PRIMARY KEY,

PersonName varchar(30) REFERENCES #table1(Name)

);

go

select * from #table1

select * from #table2

drop table #table1

The above code will not throw error. Because the length of the data type for column PersonName in second table is greater than the length of first table column Name

Malleswarareddy
I.T.Analyst
MCITP(70-451)

michaelhitchin Old Hand Points: 385 More actions · Answer 8

malleswarareddy_m (4/26/2013)
Thanks for nice question.
---Statement 1
CREATE TABLE #table1
(
ID int PRIMARY KEY,
Name varchar(20)
CONSTRAINT UN_Name UNIQUE(Name)
);
CREATE TABLE #table2
(
ID int PRIMARY KEY,
PersonName varchar(30) REFERENCES #table1(Name)
);
go
select * from #table1
select * from #table2
drop table #table1
The above code will not throw error. Because the length of the data type for column PersonName in second table is greater than the length of first table column Name

Well, I think that the reason that your script runs is not because of the wider foreign key column. I think it's because you used temporary tables and they don't enforce foreign key constraints.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 9

Yes you are right.I have verified with temp table.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

malleswarareddy_m (4/26/2013)
Thanks for nice question.
---Statement 1
CREATE TABLE #table1
(
ID int PRIMARY KEY,
Name varchar(20)
CONSTRAINT UN_Name UNIQUE(Name)
);
CREATE TABLE #table2
(
ID int PRIMARY KEY,
PersonName varchar(30) REFERENCES #table1(Name)
);
go
select * from #table1
select * from #table2
drop table #table1
The above code will not throw error. Because the length of the data type for column PersonName in second table is greater than the length of first table column Name

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Bangla Hall of Fame Points: 3137 More actions · Answer 11

Bangla

Hall of Fame

Points: 3137

April 28, 2013 at 10:43 pm

#1610667

Nice question...

vinu512 SSCoach Points: 15729 More actions · Answer 12

Very good question.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

David Conn SSCertifiable Points: 6010 More actions · Answer 13

David Conn

SSCertifiable

Points: 6010

May 5, 2013 at 10:50 pm

#1612511

Good Question. Thanks.

manik_anu SSCrazy Points: 2377 More actions · Answer 14

nice and easy question....

Manik
You cannot get to the top by sitting on your bottom.

sqlnaive SSCoach Points: 17437 More actions · Answer 15

That was an easy question without any complexities. Thanks. 🙂