Ques about Foreign Keys in Create Table command 1

[batteam]

When creating a table in SQL using the 'Create Table' command, is it necessary to specifically define a 'Foreign Key' with 'References' to the appropriate primary key table primary key column, or does the fact that the primary key column name is in the new table (to be created) and thus it automatically assume its a Foreign Key? Something like:

Create Table yyyyy (
auto_id int not null,
emp_id int Foreign Key References tablexxx (emp_id)
xxxx
xxxx

)

If necessary to specifically define a Foreign Key, does this speed up data retrieval when joining the tables, or if you join them without specifying the Foreign Key, does that make data retrieval not as efficient?
Thanks for your help.

 

[gmmastros]

is it necessary to specifically define a 'Foreign Key' with 'References' to the appropriate primary key table primary key column

Yes. You need to specify the table and column.

If necessary to specifically define a Foreign Key

No. Just because SQL Server is a relational database does not mean you need to relate your tables.

does this speed up data retrieval when joining the tables

No. Defining foreign keys will not speed up data retrieval.

if you join them without specifying the Foreign Key, does that make data retrieval not as efficient?

Foreign keys are not meant to improve performance. In fact, it's possible that they will actually cause performance problems if you are not careful.

Foreign keys are meant to keep your data nice and clean. For example, suppose you had a gender column in your table. The valid options would be NULL, M and F. You probably wouldn't want to see any other values in the column, right? Well, if you had a lookup table for gender, you could create a foreign key which will enforce the lookup table values. Gender is a contrived example, but hopefully you get the point.

Foreign keys usually reference data in a lookup table. For example, suppose you had a warehouse with tons of boxes everywhere. You might have a database to keep track of box locations. You wouldn't want someone to enter a "box location" in to the table that doesn't exist, because it's likely that the application wouldn't return this data and then you'll have a real mess on your hands.

My rule of thumb is... if you can add a foreign key to a table, you should.

One common mistake people make is.... they don't add an index for the foreign key.

Using the Box, Location, BoxLocation example....

Suppose the box table has a Primary Key of BoxId, the location table has a primary key of LocationId. The BoxLocation table would have BoxId and LocationId, right. Each of this columns would reference the other table. You should make sure you have separate indexes in the BoxLocation table for each column. The index will speed up the joins (not the foreign key).

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

Using the Box, Location, BoxLocation example...

nice design, this allows a box to be in more than one location!!

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[LarrySteele]

nice design, this allows a box to be in more than one location!!

Rudy, to be fair, that box can be in more than one location - at different times!

 

[batteam]

George: Thanks so much for taking the time to explain. I have a better understanding of Foreign Keys. In my programming - Access 2007 projects with SQL server, and using your gender example, I limit the user to 'M' or 'F' thru VBA code behind the control - on the after update method, for example.

But another question for clarification: you say "One common mistake people make is.... they don't add an index for the foreign key". Do you mean setting the table column in question as a Foreign Key AND an Index in the same Create Table command? In my example, would I also have to make emp_id an index (I'm already making it a foreign key). Thanks again for your help.

 

[gmmastros]

would I also have to make emp_id an index (I'm already making it a foreign key).

Making a column a foreign key does NOT make it an index. You should add that index separately.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There is even an interesting blog on this topic by Greg Low:

http://sqlblog.com/blogs/greg_low/a...-should-sql-server-do-that-automatically.aspx

PluralSight Learning Library

 

[markros]

And read comments to this blog, especially one by Paul Nilsen

PluralSight Learning Library