Difference between revisions of "FOREIGN KEY Table Constraint"
Yvonne.milne (Talk | contribs) |
|||
| (5 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
==Purpose== | ==Purpose== | ||
Table constraint to define a Foreign Key | Table constraint to define a Foreign Key | ||
| − | |||
==Syntax== | ==Syntax== | ||
| − | FOREIGN KEY < | + | FOREIGN KEY <column> | (<column>) [TAG <cTagName>] |
| − | + | [REFERENCES <cTableName> TAG <cTagName2>] | |
| − | [REFERENCES <cTableName> | + | |
| + | [COLLATE <cCollateSequence>] | ||
==See Also== | ==See Also== | ||
[[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE TABLE]] | [[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE TABLE]] | ||
| − | |||
==Description== | ==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. | 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 | + | 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 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== | ==Example== | ||
| Line 29: | Line 26: | ||
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,; | |
| − | + | 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 | ||
| + | </code> | ||
==Products== | ==Products== | ||
| − | + | Lianja, Lianja Server | |
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
Latest revision as of 06:38, 3 February 2016
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
