Static Wikipedia February 2008 (no images)

aa - ab - af - ak - als - am - an - ang - ar - arc - as - ast - av - ay - az - ba - bar - bat_smg - bcl - be - be_x_old - bg - bh - bi - bm - bn - bo - bpy - br - bs - bug - bxr - ca - cbk_zam - cdo - ce - ceb - ch - cho - chr - chy - co - cr - crh - cs - csb - cu - cv - cy - da - de - diq - dsb - dv - dz - ee - el - eml - en - eo - es - et - eu - ext - fa - ff - fi - fiu_vro - fj - fo - fr - frp - fur - fy - ga - gan - gd - gl - glk - gn - got - gu - gv - ha - hak - haw - he - hi - hif - ho - hr - hsb - ht - hu - hy - hz - ia - id - ie - ig - ii - ik - ilo - io - is - it - iu - ja - jbo - jv - ka - kaa - kab - kg - ki - kj - kk - kl - km - kn - ko - kr - ks - ksh - ku - kv - kw - ky - la - lad - lb - lbe - lg - li - lij - lmo - ln - lo - lt - lv - map_bms - mdf - mg - mh - mi - mk - ml - mn - mo - mr - mt - mus - my - myv - mzn - na - nah - nap - nds - nds_nl - ne - new - ng - nl - nn - no - nov - nrm - nv - ny - oc - om - or - os - pa - pag - pam - pap - pdc - pi - pih - pl - pms - ps - pt - qu - quality - rm - rmy - rn - ro - roa_rup - roa_tara - ru - rw - sa - sah - sc - scn - sco - sd - se - sg - sh - si - simple - sk - sl - sm - sn - so - sr - srn - ss - st - stq - su - sv - sw - szl - ta - te - tet - tg - th - ti - tk - tl - tlh - tn - to - tpi - tr - ts - tt - tum - tw - ty - udm - ug - uk - ur - uz - ve - vec - vi - vls - vo - wa - war - wo - wuu - xal - xh - yi - yo - za - zea - zh - zh_classical - zh_min_nan - zh_yue - zu

Web Analytics
Cookie Policy Terms and Conditions Foreign key - Wikipedia, the free encyclopedia

Foreign key

From Wikipedia, the free encyclopedia

In the context of relational databases, a foreign key (FK) is a referential constraint between two tables. The FK 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.

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 SQL:2003 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.

Contents

[edit] 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 SQL:2003 like 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 <table identifier> 
   ADD [ CONSTRAINT <constraint identifier> ] 
      FOREIGN KEY ( <column expression> {, <column expression>}... )
      REFERENCES <table identifier> [ ( <column expression> {, <column expression>}... ) ]
      [ ON UPDATE <referential action> ]
      [ ON DELETE <referential action> ]

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)
      REFERENCING other_table(UNIQUE(key_col) ON DELETE CASCADE,
   ... )

If the foreign key is comprised of 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),
   ... )

[edit] Referential Actions

Because the DBMS enforces referential constraints, it must ensure data integrity if rows in a referenced table are to deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. SQL:2003 specifies 5 different referential actions that shall take place in such occurences:

[edit] CASCADE

All dependent rows are deleted if the referenced row is deleted. Likewise, the values in the foreign key columns are updated with the respective values in the referenced row.

[edit] 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.

[edit] 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.

[edit] SET NULL

The FK 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 FK columns does not require a referenced row.

[edit] SET DEFAULT

Similarily to SET NULL, the FK values in the referencing row are set to the column default when the referenced row is updated or deleted.

[edit] 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. Keys are marked in bold.

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

The corresponding DDL is this:

 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 PRIMARY KEY,
    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 )

[edit] Example 2

A company has several departments, and each employee is a member of one department. This is enforced in the database by a foreign key on the Employee table, which refers to the Department table. Imagine there is a relation for departments. Each department has a department name, and a surrogate key for the department called "DepartmentID". The employee relation would have "DepartmentID" as an attribute, with a foreign key reference to the aforementioned surrogate key of the department relation. The DBMS would then enforce that each employee could not be created without specifiying a valid department row, and department row could not be deleted if there are employees referencing that department.

[edit] See also


Topics in database management systems (DBMS) ( view talk edit )

Concepts
DatabaseDatabase modelRelational databaseRelational modelRelational algebraPrimary key, Foreign key, Surrogate key, Superkey, Candidate keyDatabase normalizationReferential integrityRelational DBMSDistributed DBMSACID

Objects
TriggerViewTableCursorLogTransactionIndexStored procedurePartition

Topics in SQL
SelectInsertUpdateMergeDeleteJoinUnionCreateDrop

Implementations of database management systems

Types of implementations
RelationalFlat fileDeductiveDimensionalHierarchicalObject orientedTemporalXML data stores

Components
Query languageQuery optimizerQuery planODBCJDBC

Database products

Apache DerbyBerkeley DBCachéDB2db4oDBaseeXtremeDBFilemaker ProFirebirdGreenplumH2HelixInformixIngresInterBaseLinterMicrosoft AccessMicrosoft SQL ServerMimer SQLMonetDBMySQLObjectivity/DBOpenLink VirtuosoOpenOffice.org BaseOracleOracle RdbParadoxPerstPostgreSQLSQLiteSybase IQSybaseTeradataUniVerseVisual FoxPro


Other: Object-oriented (comparison) • relational (comparison)

Static Wikipedia 2008 (no images)

aa - ab - af - ak - als - am - an - ang - ar - arc - as - ast - av - ay - az - ba - bar - bat_smg - bcl - be - be_x_old - bg - bh - bi - bm - bn - bo - bpy - br - bs - bug - bxr - ca - cbk_zam - cdo - ce - ceb - ch - cho - chr - chy - co - cr - crh - cs - csb - cu - cv - cy - da - de - diq - dsb - dv - dz - ee - el - eml - en - eo - es - et - eu - ext - fa - ff - fi - fiu_vro - fj - fo - fr - frp - fur - fy - ga - gan - gd - gl - glk - gn - got - gu - gv - ha - hak - haw - he - hi - hif - ho - hr - hsb - ht - hu - hy - hz - ia - id - ie - ig - ii - ik - ilo - io - is - it - iu - ja - jbo - jv - ka - kaa - kab - kg - ki - kj - kk - kl - km - kn - ko - kr - ks - ksh - ku - kv - kw - ky - la - lad - lb - lbe - lg - li - lij - lmo - ln - lo - lt - lv - map_bms - mdf - mg - mh - mi - mk - ml - mn - mo - mr - mt - mus - my - myv - mzn - na - nah - nap - nds - nds_nl - ne - new - ng - nl - nn - no - nov - nrm - nv - ny - oc - om - or - os - pa - pag - pam - pap - pdc - pi - pih - pl - pms - ps - pt - qu - quality - rm - rmy - rn - ro - roa_rup - roa_tara - ru - rw - sa - sah - sc - scn - sco - sd - se - sg - sh - si - simple - sk - sl - sm - sn - so - sr - srn - ss - st - stq - su - sv - sw - szl - ta - te - tet - tg - th - ti - tk - tl - tlh - tn - to - tpi - tr - ts - tt - tum - tw - ty - udm - ug - uk - ur - uz - ve - vec - vi - vls - vo - wa - war - wo - wuu - xal - xh - yi - yo - za - zea - zh - zh_classical - zh_min_nan - zh_yue - zu -

Static Wikipedia 2007 (no images)

aa - ab - af - ak - als - am - an - ang - ar - arc - as - ast - av - ay - az - ba - bar - bat_smg - bcl - be - be_x_old - bg - bh - bi - bm - bn - bo - bpy - br - bs - bug - bxr - ca - cbk_zam - cdo - ce - ceb - ch - cho - chr - chy - co - cr - crh - cs - csb - cu - cv - cy - da - de - diq - dsb - dv - dz - ee - el - eml - en - eo - es - et - eu - ext - fa - ff - fi - fiu_vro - fj - fo - fr - frp - fur - fy - ga - gan - gd - gl - glk - gn - got - gu - gv - ha - hak - haw - he - hi - hif - ho - hr - hsb - ht - hu - hy - hz - ia - id - ie - ig - ii - ik - ilo - io - is - it - iu - ja - jbo - jv - ka - kaa - kab - kg - ki - kj - kk - kl - km - kn - ko - kr - ks - ksh - ku - kv - kw - ky - la - lad - lb - lbe - lg - li - lij - lmo - ln - lo - lt - lv - map_bms - mdf - mg - mh - mi - mk - ml - mn - mo - mr - mt - mus - my - myv - mzn - na - nah - nap - nds - nds_nl - ne - new - ng - nl - nn - no - nov - nrm - nv - ny - oc - om - or - os - pa - pag - pam - pap - pdc - pi - pih - pl - pms - ps - pt - qu - quality - rm - rmy - rn - ro - roa_rup - roa_tara - ru - rw - sa - sah - sc - scn - sco - sd - se - sg - sh - si - simple - sk - sl - sm - sn - so - sr - srn - ss - st - stq - su - sv - sw - szl - ta - te - tet - tg - th - ti - tk - tl - tlh - tn - to - tpi - tr - ts - tt - tum - tw - ty - udm - ug - uk - ur - uz - ve - vec - vi - vls - vo - wa - war - wo - wuu - xal - xh - yi - yo - za - zea - zh - zh_classical - zh_min_nan - zh_yue - zu -

Static Wikipedia 2006 (no images)

aa - ab - af - ak - als - am - an - ang - ar - arc - as - ast - av - ay - az - ba - bar - bat_smg - bcl - be - be_x_old - bg - bh - bi - bm - bn - bo - bpy - br - bs - bug - bxr - ca - cbk_zam - cdo - ce - ceb - ch - cho - chr - chy - co - cr - crh - cs - csb - cu - cv - cy - da - de - diq - dsb - dv - dz - ee - el - eml - eo - es - et - eu - ext - fa - ff - fi - fiu_vro - fj - fo - fr - frp - fur - fy - ga - gan - gd - gl - glk - gn - got - gu - gv - ha - hak - haw - he - hi - hif - ho - hr - hsb - ht - hu - hy - hz - ia - id - ie - ig - ii - ik - ilo - io - is - it - iu - ja - jbo - jv - ka - kaa - kab - kg - ki - kj - kk - kl - km - kn - ko - kr - ks - ksh - ku - kv - kw - ky - la - lad - lb - lbe - lg - li - lij - lmo - ln - lo - lt - lv - map_bms - mdf - mg - mh - mi - mk - ml - mn - mo - mr - mt - mus - my - myv - mzn - na - nah - nap - nds - nds_nl - ne - new - ng - nl - nn - no - nov - nrm - nv - ny - oc - om - or - os - pa - pag - pam - pap - pdc - pi - pih - pl - pms - ps - pt - qu - quality - rm - rmy - rn - ro - roa_rup - roa_tara - ru - rw - sa - sah - sc - scn - sco - sd - se - sg - sh - si - simple - sk - sl - sm - sn - so - sr - srn - ss - st - stq - su - sv - sw - szl - ta - te - tet - tg - th - ti - tk - tl - tlh - tn - to - tpi - tr - ts - tt - tum - tw - ty - udm - ug - uk - ur - uz - ve - vec - vi - vls - vo - wa - war - wo - wuu - xal - xh - yi - yo - za - zea - zh - zh_classical - zh_min_nan - zh_yue - zu