FOREIGN KEY Table Constraint

Purpose

Table constraint to define a Foreign Key

Syntax

FOREIGN KEY <column> | (<column>) [TAG <cTagName>] [REFERENCES <cTableName> TAG <cTagName2>]

[COLLATE <cCollateSequence>]

See Also

ALTER TABLE, CONSTRAINTS, CREATE TABLE

Description

A constraint is used to define rules that help to provide data integrity. TABLE constraints apply to table-based operations. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.

The FOREIGN KEY table constraint is used to define <expr> as a Foreign Key for a parent table. A tag index is built on the specified <column>; it is given the name as defined in <cTagName>. If <cTagName> is not specified, the tag index is given the same name as the <column>. A table may have more than one Foreign Key index.

The REFERENCES clause is used to create a relationship from the column, <column>, to an index key of another table. The value of the <column> is validated by checking that it already exists as a value in the referenced index key.

The name of the referenced table is specified in <cTableName>. The index tag to reference is specified in <cTagName2>.

The optional COLLATE <cCollateSequence> clause is included for Visual FoxPro language compatibility only.

Example

CREATE TABLE supplier;
 (SuppId int PRIMARY KEY,;
  SuppRef char(10) UNIQUE,;
  SuppName char(40) UNIQUE)
 
CREATE TABLE purchase_order;
 (POId int PRIMARY KEY,;
  PO_SuppRef char(10),;
  POtotal num(10,2))
 
ALTER TABLE purchase_order ADD FOREIGN KEY po_suppref TAG fk_po_suppref 
ALTER TABLE purchase_order ADD FOREIGN KEY (po_suppref) TAG fk_po_suppref;
 REFERENCES supplier TAG SuppRef

Products

Lianja, Lianja Server