In the context of relational databases, a foreign key is a referential constraint between two tables. [ [http://www.visualcase.com/kbase/database_basics_-_foreign_keys.htm For a simpler visualization, see VisualCase.com] ] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.
The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in as self-referencing or recursive foreign key.
A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.
Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.
Defining Foreign Keys
Foreign keys are defined in the ANSI SQL Standard, through a FOREIGN KEY constraint. 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.
CREATE TABLE table_name ( id INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER, ... CONSTRAINT col3_fk FOREIGN KEY(col3) REFERENCES other_table(key_col) 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 table_name ( id INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER REFERENCES other_table(column_name), ... )
Referential Actions
Because the Database Management System enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. specifies 5 different referential actions that shall take place in such occurrences: * CASCADE * RESTRICT * NO ACTION * SET NULL * SET DEFAULT
CASCADE
Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well. A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.
Example Tables: Customer(customer_id,cname,caddress)and Order(customer_id,products,payment)
Customer is the master table and Order is the child table, where 'customer_id' is the foreign key in Order and represents the customer who placed the order. When a row of Customer is deleted, any Order row matching the deleted Customer's customer_id will also be deleted.the values are deleted in the row like if we delete one row in the parent table then the same row in the child table will be automatically deleted.
RESTRICT
A row in the referenced table cannot be updated or deleted if dependent rows still exist. In that case, no data change is even attempted. Should not be allowed.
NO ACTION
The UPDATE or DELETE SQL statement is executed on the referenced table. The DBMS verifies at the end of the statement execution if none of the referential relationships is violated. The major difference to RESTRICT is that triggers or the statement semantics itself may give a result in which no foreign key relationships is violated. Then, the statement can be executed successfully.
"SET NULL"
The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.
SET DEFAULT
Similarly to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.
Example 1
As a first example to illustrate foreign keys, suppose an accounts database has a table with invoices and each invoice is associated with a particular supplier. Supplier details (such as address or phone number) are kept in a separate table; each supplier is given a 'supplier number' to identify them. Each invoice record has an attribute containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table. The foreign key in the Invoices table points to that primary key. The relational schema is the following. Primary keys are marked in bold, and foreign keys are marked in italics.
Supplier ( SupplierNumber, Name, Address, Type ) Invoices ( InvoiceNumber, "SupplierNumber", Text )
The corresponding Data Definition Language statement is as follows. CREATE TABLE Supplier ( SupplierNumber INTEGER NOT NULL, Name VARCHAR(20) NOT NULL, Address VARCHAR(50) NOT NULL, Type VARCHAR(10), CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber), CONSTRAINT number_value CHECK (SupplierNumber > 0) ) CREATE TABLE Invoices ( InvoiceNumber INTEGER NOT NULL, SupplierNumber INTEGER NOT NULL, Text VARCHAR(4096), 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 )
Foreign Key — Ein Schlüssel dient in einer Relationalen Datenbank dazu, die Tupel einer Relation eindeutig zu identifizieren. Anschaulich kann man sich eine Relation als Tabelle vorstellen. Ein Schlüssel für eine solche Tabelle ist dann eine Gruppe von Spalten … Deutsch Wikipedia
foreign key — noun A field in a relational database record that points to a key field in another table … Wiktionary
Foreign — may refer to:*Foreign corporation, a corporation that can do business outside its jurisdiction *Foreign key, a constraint in a relational database *Foreign language, a language not spoken by the people of a certain place *Foreign policy, how a… … Wikipedia
Key field — A key field is a field with cows in or set of fields of a database (typically a relational database) table which together form a unique identifier for a database record (a table entry). The aggregate of these fields is usually referred to simply… … Wikipedia
Foreign internal defense — (FID) is used by a number of Western militaries, explicitly by the United States but sharing ideas with countries including France and the United Kingdom, to describe an approach to combating actual or threatened insurgency in a foreign state… … Wikipedia
Foreign relations of South Africa — The foreign relations of South Africa have spanned from the country s time as Dominion and later Realm of the British Empire to its isolationist policies under Apartheid to its position as a responsible international actor taking a key role in… … Wikipedia
Foreign Reports — Inc. is a Washington, D.C. based consulting firm for the oil industry, founded in 1956. [http://www.foreignreports.com/ Foreign Reports] advises energy companies, governments, and financial institutions on world energy issues, with a… … Wikipedia
Foreign relations of the Philippines — is administered by the Philippines President and the nation s Department of Foreign Affairs. Much of the republic s international relations are dominated by the Philippines ties to the United States, of which the Philippines was historically a… … Wikipedia
Foreign Policy Association — (FPA) Type Non profit Founded 1918, New York, Headquarters New York City, USA Website … Wikipedia
Foreign policy analysis — (FPA) is the systematic study of and research into the processes and theories of foreign policy.The study of foreign policyForeign policy analysis involves the study of how a state makes foreign policy.Because FPA involves the study of both… … Wikipedia
18+© Academic, 2000-2024