473,325 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Do foreign keys generate implicit indexes?

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bob

Sep 21 '07 #1
6 5884
bo******@gmail.com (bo******@gmail.com) writes:
If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID?
In SQL Server, no.
I've been told this is done in some databases, but i need to know for
sure if SQL Server does it. Has anyone heard of this before, on any
other databses perhaps?
I seem to recall having heard this about Sybase Anywhere.
Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 21 '07 #2
>I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.

Sep 21 '07 #3
On Fri, 21 Sep 2007 19:51:14 -0000, "bo******@gmail.com"
<bo******@gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

>If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bob
Sep 23 '07 #4
Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" <no*************@cox.netwrote in message
news:8q********************************@4ax.com...
On Fri, 21 Sep 2007 19:51:14 -0000, "bo******@gmail.com"
<bo******@gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

>>If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bob
Sep 23 '07 #5
Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL


<bo******@gmail.comwrote in message
news:11**********************@n39g2000hsh.googlegr oups.com...
If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bob

Sep 24 '07 #6
On Sep 24, 3:25 am, "Jack Vamvas" <DEL_TO_RE...@del.comwrote:
Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL

<bobdu...@gmail.comwrote in message

news:11**********************@n39g2000hsh.googlegr oups.com...
If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
Heres an example of how the foreign key constraint is being added:
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
Bob
Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimbe...eyColumns.aspx

Sep 24 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Olivier Crèvecoeur | last post by:
Hello, Excuse me for my poor english. I would kike know if create index on the foreign key it's necessary or if Oracle, are optimized for using foreign key whithout index. Best regards ...
3
by: D | last post by:
Database 1 is corrupted. Database 2 was created by dba but none of the primary or foreign key constraints were ported over. TOAD won't let me export. I will try ErWin next. What is the best way...
1
by: Vinodh Kumar P | last post by:
I understand the number of foreign keys allowed is restricted by the DBMS I use. In a general relational schema design perspective how many foreign keys a table shall have? If I have large number...
0
by: Christopher Boomer | last post by:
I am relatively new to the world of XML and python, and I think I am still missing some of the basic concepts. It cannot be as hard as I think, or programmers would be flocking to it the way they...
26
by: pb648174 | last post by:
I have a table called BidItem which has another table called BidAddendum related to it by foreign key. I have another table called BidFolder which is related to both BidItem and BidAddendum, based...
6
by: Jean-Christian Imbeault | last post by:
Is it right for postgres to accept a foreign key constraint when the type of the field is not the same as that of the foreign key? For example: # Create table a (id int primary key); NOTICE: ...
6
by: Brendan Jurd | last post by:
Hi all, I read on the manual page for Inheritance that: "A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single...
2
by: kal stevens | last post by:
I have been trying to write a database schema in mysql, and I cant figure this out. Here is a database schema DROP DATABASE IF EXISTS d; CREATE DATABASE d;
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.