Difference between revisions of "FOREIGN KEY Table Constraint"
Helen.george (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
| Line 3: | Line 3: | ||
==Syntax== | ==Syntax== | ||
| − | FOREIGN KEY < | + | FOREIGN KEY <column> | (<column>) [TAG <cTagName>] [COLLATE <cCollateSequence>] |
| − | + | FOREIGN KEY <column> | (<column>) REFERENCES <cTableName> TAG <cTagName2> | |
==See Also== | ==See Also== | ||
| Line 13: | Line 13: | ||
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. | 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 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 optional COLLATE <cCollateSequence> clause is included for Visual FoxPro language compatibility only. | The optional COLLATE <cCollateSequence> clause is included for Visual FoxPro language compatibility only. | ||
| − | + | FOREIGN KEY with the REFERENCES clause is used to create a relationship from a 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 name of the referenced table is specified in <cTableName>. The index tag to reference is specified in <cTagName2>. |
==Example== | ==Example== | ||
| Line 25: | Line 25: | ||
CREATE TABLE supplier; | CREATE TABLE supplier; | ||
| − | + | (SuppId int PRIMARY KEY,; | |
| + | SuppRef char(10) UNIQUE,; | ||
| + | SuppName char(40) UNIQUE) | ||
| + | |||
CREATE TABLE purchase_order; | CREATE TABLE purchase_order; | ||
| − | + | (POId int PRIMARY KEY,; | |
| − | ALTER TABLE purchase_order | + | 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) REFERENCES supplier TAG SuppRef | ||
</code> | </code> | ||
Revision as of 10:51, 29 January 2016
Purpose
Table constraint to define a Foreign Key
Syntax
FOREIGN KEY <column> | (<column>) [TAG <cTagName>] [COLLATE <cCollateSequence>]
FOREIGN KEY <column> | (<column>) 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. 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 optional COLLATE <cCollateSequence> clause is included for Visual FoxPro language compatibility only.
FOREIGN KEY with the REFERENCES clause is used to create a relationship from a 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>.
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) REFERENCES supplier TAG SuppRef
Products
Lianja, Lianja Server
