Foreign key

Uses your system voice; voice and rate in settings.

Snapshot

≈ 4 min read

Names in other languages

No names in other languages are held for this entry yet.

Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.

A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables. In the context of relational databases, a foreign key is subject to an inclusion dependency constraint that the tuples consisting of the foreign key attributes in one relation, R, must also exist in some other (not necessarily distinct) relation, S; furthermore that those attributes must also be a candidate key in S. In other words, a foreign key is a set of attributes that a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table. Since MEMBER_NAME is a foreign key, any value existing as the name of a member in TEAM must also exist as a person's name in the PERSON table; in other words, every member of a TEAM is also a PERSON.

A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables. In the context of relational databases, a foreign key is subject to an inclusion dependency constraint that the tuples consisting of the foreign key attributes in one relation, R, must also exist in some other (not necessarily distinct) relation, S; furthermore that those attributes must also be a candidate key in S. In other words, a foreign key is a set of attributes that a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table. Since MEMBER_NAME is a foreign key, any value existing as the name of a member in TEAM must also exist as a person's name in the PERSON table; in other words, every member of a TEAM is also a PERSON. CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER, col4 INTEGER, FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE ) If the foreign key is a single column only, the column can be marked as such using the following syntax: CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER, col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE ) Foreign keys can be defined with a stored procedure statement. sp_foreignkey child_table, parent_table, col3, col4 child_table: the name of the table or view that contains the foreign key to be defined. parent_table: the name of the table or view that has the primary key to which the foreign key applies. The primary key must already be defined. col3 and col4: the name of the columns that make up the foreign key. The foreign key must have at least one column and at most eight columns. CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; CREATE TABLE Supplier ( SupplierNumber INTEGER NOT NULL, Name VARCHAR(20) NOT NULL, Address VARCHAR(50) NOT NULL, CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber), CONSTRAINT number_value CHECK(SupplierNumber > 0) ) CREATE TABLE Invoice ( InvoiceNumber INTEGER NOT NULL, Text VARCHAR(4096), SupplierNumber INTEGER NOT NULL, 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 )

Text derived from https://en.wikipedia.org/w/index.php?oldid=1270418735 under CC BY-SA 4.0; modifications have been made.

Sources cited in this revision

4 named source(s) extracted from the cited revision. A source with no author is shown by its publication; nothing is invented.