In the context of relational databases, a foreign key is a referential constraint between two tables.^[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referenced table must form a primary key or unique key. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table. This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in as self-referencing or recursive foreign key.

A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.

Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.

Defining Foreign Keys

Foreign keys are defined in the ANSI SQL Standard, through a FOREIGN KEY constraint. The syntax to add such a constraint to an existing table is defined in as shown below. Omitting the column list in the REFERENCES clause implies that the foreign key shall reference the primary key of the referenced table.

>
ALTER TABLE  
   ADD [ CONSTRAINT  ] 
      FOREIGN KEY (  {, }... )
      REFERENCES  [ (  {, }... ) ]
      [ ON UPDATE  ]
      [ ON DELETE  ]




Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.



>
CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER,
   ...
   CONSTRAINT col3_fk FOREIGN KEY(col3)
      REFERENCES other_table(key_col) ON DELETE CASCADE,
   ... )




If the foreign key is a single column only, the column can be marked as such using the following syntax:



>
CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER FOREIGN KEY REFERENCES other_table(column_name),
   ... )




In PostgreSQL 8.1, the foreign key syntax is slightly different. The CREATE TABLE directive omits the FOREIGN KEY identifier:



>
CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER REFERENCES other_table(column_name),
   ... )

Referential Actions
Because the DBMS enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. specifies 5 different referential actions that shall take place in such occurrences:
CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

CASCADE
Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well.A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.



Example Tables: Customer(cid,cname,caddress), Order(cid,products,payment)



Customer is the master table and Order is the child table, where 'cid' is the foreign key in Order and represents the customer who placed the order. When a row of Customer is deleted, any Order row matching the deleted Customer's cid will also be deleted.



RESTRICT
A row in the referenced table cannot be updated or deleted if dependent rows still exist. In that case, no data change is even attempted.



NO ACTION
The UPDATE or DELETE SQL statement is executed on the referenced table. The DBMS verifies at the end of the statement execution if none of the referential relationships is violated. The major difference to RESTRICT is that triggers or the statement semantics itself may give a result in which no foreign key relationships is violated. Then, the statement can be executed successfully.



SET NULL
The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.



SET DEFAULT
Similarly to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.
Example 1
As a first example to illustrate foreign keys, suppose an accounts database has a table with invoices and each invoice is associated with a particular supplier. Supplier details (such as address or phone number) are kept in a separate table; each supplier is given a 'supplier number' to identify them. Each invoice record has an attribute containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table. The foreign key in the Invoices table points to that primary key. The relational schema is the following. Primary keys are marked in bold, foreign keys are in italics.



Supplier ( SupplierNumber, Name, Address, Type )
Invoices ( InvoiceNumber, SupplierNumber, Text )



The corresponding Data Definition Language statement is as follows.



CREATE TABLE Supplier (
SupplierNumber INTEGER NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
Type VARCHAR(10),
CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
CONSTRAINT number_value CHECK (SupplierNumber > 0) )



CREATE TABLE Invoices (
InvoiceNumber INTEGER NOT NULL,
SupplierNumber INTEGER NOT NULL,
Text VARCHAR(4096),
CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
CONSTRAINT supplier_fk FOREIGN KEY(SupplierNumber)
REFERENCES Supplier(SupplierNumber)
ON UPDATE CASCADE ON DELETE RESTRICT )
See also
alternate key
candidate key
compound key
superkey

References
1. ^ For a simpler visualization, see VisualCase.com






Topics in database management systems (DBMS)
 •  • [ edit]


Concepts

Database 
Database models 
Database storage 
Relational model 
Distributed DBMS 
ACID 
Null

Relational database 
Relational algebra 
Relational calculus 
Database normalization 
Referential integrity 
Relational DBMS 

Primary key, Foreign key, Surrogate key, Superkey, Candidate key


Objects

Trigger 
View 
Table 
Cursor 
Log 
Transaction 
Index 

Stored procedure 
Partition

Topics in SQL

Select 
Insert 
Update 
Merge 
Delete 
Join 
Union 
Create 
Drop

Begin work 
Commit 
Rollback 
Truncate 
Alter

Implementations of database management systems


Types of implementations

Relational 
Flat file 
Deductive 
Dimensional 
Hierarchical 
Object oriented 
Object relational 
Temporal 
XML data stores


Database products

Object-oriented (comparison) 
Relational (comparison)

Components

Query language 
Query optimizer 
Query plan 
ODBC 
JDBC
 A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information. 
 referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table.
..... Click the link for more information. 
 In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information. 
 In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information. 
 Information is the result of processing, gathering, manipulating and organizing data in a way that adds to the knowledge of the receiver. In other words, it is the context in which data is taken.
..... Click the link for more information. 
 Database normalization  is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
..... Click the link for more information. 
 A database system or DBS is a system or software designed to manage a database, and run operations on the data requested by numerous clients. Typical examples of DBS use include accounting, human resources and customer support systems.
..... Click the link for more information. 
 PostgreSQL is an object-relational database management system (ORDBMS). It is released under a BSD-style license and is thus free software. As with many other open-source programs, PostgreSQL is not controlled by any single company, but relies on a global community of developers
..... Click the link for more information. 
 
..... Click the link for more information. 
 An SQL DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed. 
Usage
 The DELETE statement has this syntax:

DELETE FROM

..... Click the link for more information. 
 A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.
..... Click the link for more information. 
 Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information. 
 A Data Definition Language (DDL) is a computer language for defining data structures. 
XML Schema
 XML Schema is an example of a pure DDL (although only relevant in the context of XML). 
SQL
 A subset of SQL's instructions form another DDL.
..... Click the link for more information. 
 An alternate key (or secondary key) is any candidate key which is not selected to be the primary key (PK).

For example, a relational database with a table "employee" could have attributes like "employee_id", "bank_acct_no", and so on.
..... Click the link for more information. 
 In the relational model, a candidate key of a relvar (relation variable) is a set of attributes of that relvar such that 
(1) at all times it holds in the relation assigned to that variable that there are no two distinct tuples with the same values for these attributes and

..... Click the link for more information. 
 In database design, a compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.

No restriction is applied to the attributes regarding their (initial) ownership within the data model.
..... Click the link for more information. 
 superkey is defined in the relational model of database organisation as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in
..... Click the link for more information. 
 A database management system (DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include Oracle, DB2, Microsoft Access, Microsoft SQL Server, PostgreSQL, MySQL, FileMaker and Sybase Adaptive Server Enterprise.
..... Click the link for more information. 
 database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
..... Click the link for more information. 
 A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join.
..... Click the link for more information. 
 flat file database describes any of various means to encode a data model (most commonly a table) as a plain text file. 
Unordered
 Unordered storage typically stores the records in the order they are inserted, while having 
..... Click the link for more information. 
 The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to
..... Click the link for more information. 
 A distributed database management system is a software system that permits the management of a distributed database and makes the distribution transparent to the users. A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network.
..... Click the link for more information. 
 ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
..... Click the link for more information. 
 Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information. 
 A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information. 
 Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. Operators operate on one or more relations to yield a relation. Relational algebra is a part of computer science.
..... Click the link for more information. 
 The relational calculus refers to the two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and that provide a declarative way to specify database queries.
..... Click the link for more information. 
 Database normalization  is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
..... Click the link for more information. 
 referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table.
..... Click the link for more information.