Difference between revisions of "FOREIGN KEY Table Constraint"

From Recital Documentation Wiki
Jump to: navigation, search
 
(One intermediate revision by one user not shown)
Line 27: Line 27:
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
set sql to vfp
+
 
CREATE TABLE supplier (SuppId i PRIMARY KEY, SuppName c(40) UNIQUE)
+
CREATE TABLE supplier;
CREATE TABLE purchase_order (POid i PRIMARY KEY, SuppId i, POtotal n(10,2) )
+
  (SuppId i PRIMARY KEY, SuppName c(40) UNIQUE)
ALTER TABLE purchase_order ADD FOREIGN KEY SuppID TAG SuppId REFERENCES supplier
+
CREATE TABLE purchase_order;
 +
  (POid i PRIMARY KEY, SuppId i, POtotal n(10,2))
 +
ALTER TABLE purchase_order;
 +
  ADD FOREIGN KEY SuppID TAG SuppId REFERENCES supplier
 
</code>
 
</code>
  
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital, Recital Server
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]

Latest revision as of 10:35, 22 December 2009

Purpose

Table constraint to define a Foreign Key


Syntax

FOREIGN KEY <expr> TAG <cTagName> [COLLATE <cCollateSequence>]

[REFERENCES <cTableName> [TAG <cTagName2>]]


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. The <expr> can contain any valid index key. A tag index is built on the specified <expr>; it is given the name as defined in <cTagName>. A table may have more than one Foreign Key index.

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

The optional REFERENCES clause is used to create a relationship to an index key of another table. The value of the <expr> 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>. If the optional TAG <cTagName2> clause is omitted, the primary index key of <cTableName> is used. If <cTableName> has no index tags, an error is generated.


Example

CREATE TABLE supplier;
  (SuppId i PRIMARY KEY, SuppName c(40) UNIQUE)
CREATE TABLE purchase_order;
  (POid i PRIMARY KEY, SuppId i, POtotal n(10,2))
ALTER TABLE purchase_order;
  ADD FOREIGN KEY SuppID TAG SuppId REFERENCES supplier


Products

Recital, Recital Server