x

What is a foreign key?

I see this listed in article all the time. What does it mean to have a foreign key and how do I set one up in SQL Server?

more ▼

asked Oct 29 '09 at 07:33 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest

A Foreign Key (FK) in a SQL table’s field refers to a look-up (reference) field in another table. A simple example:

dbo.tblCustomers has a field name “StateID”. One would probably make this field a Foreign Key (FK) so that it would only take entries from a specific table’s specific field (e.g. dbo.tblLIBStates field name “StateID”). FK ensures correctness of data (e.g naming conventions, etc) in the primary table.

ALTER TABLE [dbo].[tblCustomers] WITH CHECK ADD CONSTRAINT [FK_tblCustomers_tblLIBStates_StateID] FOREIGN KEY([StateID]) REFERENCES [dbo].tblLIBStates 
more ▼

answered Oct 29 '09 at 11:14 PM

Mansour Shoari gravatar image

Mansour Shoari
435 13 14 15

(comments are locked)
10|1200 characters needed characters left

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.

more ▼

answered Oct 30 '09 at 06:39 AM

Nitin gravatar image

Nitin
12 1 1 1

A foreign key can reference any candidate key. The target of a foreign key doesn't have to be the primary key. It could also be in the same table - it doesn't have to be in another table.

Additionally, SQL Server will let you create a FOREIGN KEY constraint that matches the column list of any unique index.
Oct 30 '09 at 09:46 AM David 1
I should have used a broader and more correct term for the purpose of FK "i.e. referential integrity of the data" in my posting. Thank you for pointing this out, Nitin.
Oct 30 '09 at 11:10 AM Mansour Shoari
(comments are locked)
10|1200 characters needed characters left

For completeness:

A foreign key is a reference from a value or set of values in one table, to a value or set of values in a 'target' or 'referenced' table, which can be a different table or the same table as the 'primary' or 'parent' table.

The target value set must be NON-NULLABLE, and enforced unique through either a PRIMARY KEY constraint, a UNIQUE constraint or a UNIQUE INDEX.

Foreign keys can be enabled or disabled, although disabling foreign keys can sometimes cause the query optimiser to make incorrect assumptions about the presence of data in the target table in older versions of SQL Server.

Foreign keys also have referential actions which are invokes when a row in a target table that is referenced by a row in the primary table is deleted, or any of the values in the set of values are updated. These actions are:

  • NONE - An error is produced
  • CASCADE - The operation 'cascades' meaning that the same action is taken in the primary table
  • SET NULL (2005+) - The referencing values in the primary table are set to NULL
  • SET DEFAULT (2005+) - The referencing values in the primary table are set to their default values provides through DEFAULT constraints

Foreign keys can also be marked as 'NOT FOR REPLICATION' which means that the constraint is not enforced when replication agents perform operations on the data.

The MSDN reference to the table_constraint clause of ALTER TABLE can be found here.

more ▼

answered Oct 30 '09 at 09:29 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Aug 03 '13 at 10:36 AM

VIJJA gravatar image

VIJJA
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x122
x107
x32

asked: Oct 29 '09 at 07:33 PM

Seen: 4893 times

Last Updated: Aug 03 '13 at 10:36 AM