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 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.
Summary
Since the purpose of the foreign key is to identify a particular row of referenced table, it is generally required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value.). This rule is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table.
For example, consider a database with two tables: a CUSTOMER table that includes all customer data and an ORDER table that includes all customer orders. Suppose the business requires that each order must refer to a single customer. To reflect this in the database, a foreign key column is added to the ORDER table (e.g., CUSTOMERID), which references the primary key of CUSTOMER (e.g. ID). Because the primary key of a table must be unique, and because CUSTOMERID only contains values from that primary key field, we may assume that, when it has a value, CUSTOMERID will identify the particular customer which placed the order. However, this can no longer be assumed if the ORDER table is not kept up to date when rows of the CUSTOMER table are deleted or the ID column altered, and working with these tables may become more difficult. Many real world databases work around this problem by 'inactivating' rather than physically deleting master table foreign keys, or by complex update programs that modify all references to a foreign key when a change is needed.
Foreign keys play an essential role in database design. One important part of database design is making sure that relationships between real-world entities are reflected in the database by references, using foreign keys to refer from one table to another. Another important part of database design is database normalization, in which tables are broken apart and foreign keys make it possible for them to be reconstructed.
Multiple rows in the referencing (or child) table may refer to the same row in the referenced (or parent) table. In this case, the relationship between the two tables is called a one to many relationship between the referencing table and the referenced table.
In addition, the child and parent table may, in fact, be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in as a self-referencing or recursive foreign key. In database management systems, this is often accomplished by linking a first and second reference to the same table.
A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.
A foreign key is defined as an attribute or set of attributes in a relation whose values match a primary key in another relation. The syntax to add such a constraint to an existing table is defined in as shown below. Omitting the column list in the REFERENCES clause implies that the foreign key shall reference the primary key of the referenced table. Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.
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:
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.
- 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.
Referential actions
- CASCADE
- RESTRICT
- NO ACTION
- SET NULL
- SET DEFAULT
CASCADE
RESTRICT
Similarly, a row cannot be deleted as long as there is a reference to it from a referencing or child table.
To understand RESTRICT (and CASCADE) better, it may be helpful to notice the following difference, which might not be immediately clear. The referential action CASCADE modifies the "behavior" of the (child) table itself where the word CASCADE is used. For example, ON DELETE CASCADE effectively says "When the referenced row is deleted from the other table (master table), then delete also from me". However, the referential action RESTRICT modifies the "behavior" of the master table, not the child table, although the word RESTRICT appears in the child table and not in the master table! So, ON DELETE RESTRICT effectively says: "When someone tries to delete the row from the other table (master table), prevent deletion from that other table (and of course, also don't delete from me, but that's not the main point here)."
RESTRICT is not supported by Microsoft SQL 2012 and earlier.
NO ACTION
In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the Database trigger or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.
SET NULL, SET DEFAULT
Triggers
Another important limitation appears with transaction isolation: your changes to a row may not be able to fully cascade because the row is referenced by data your transaction cannot "see", and therefore cannot cascade onto. An example: while your transaction is attempting to renumber a customer account, a simultaneous transaction is attempting to create a new invoice for that same customer; while a CASCADE rule may fix all the invoice rows your transaction can see to keep them consistent with the renumbered customer row, it won't reach into another transaction to fix the data there; because the database cannot guarantee consistent data when the two transactions commit, one of them will be forced to roll back (often on a first-come-first-served basis.)
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
Example
Supplier ('''SupplierNumber''', Name, Address)
Invoice ('''InvoiceNumber''', Text, ''SupplierNumber'')
The corresponding Data Definition Language statement is as follows.
CREATE TABLE Invoice (
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)
)
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
)
See also
External links
- SQL-99 Foreign Keys
- PostgreSQL Foreign Keys
- MySQL Foreign Keys
- FirebirdSQL primary Keys
- SQLite support for Foreign Keys
- Microsoft SQL 2012 table_constraint (Transact-SQL)
|
|
