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, 2009 at 07:33 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 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, 2009 at 11:14 PM

Mansour Shoari gravatar image

Mansour Shoari
435 14 14 16

(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, 2009 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, 2009 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, 2009 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, 2009 at 09:29 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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

answered Aug 03, 2013 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.

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:

x131
x108
x36

asked: Oct 29, 2009 at 07:33 PM

Seen: 5852 times

Last Updated: Aug 03, 2013 at 10:36 AM