Foreign key is the key which is reference from another table. In other words, Foreign Key is a Primary key on other table (Parent table) and that can be referred in current table(child table) with other column name or same column name.
Foreign key can be defined in either CREATE TABLE or ALTER TABLE. Foreign key definition is optional in CREATE TABLE or ALTER TABLE.
Foreign key can have a NULL values in the table (Child table) where it is declared as Foreign key. Foreign key will create the referential constraint between the Parent table and Child table.
Syntax
FOREIGN KEY(Column1, Column2..) REFERNCES Source-table CASCADE/RESTRICT/SET NULL
Use foreign keys to enforce referential relationships between tables. A foreign key is a column or set of columns that references the parent key in the parent table.
Foreign key constraints (also known as referential constraints or referential integrity constraints) enable definition of required relationships between and within tables. For example, a typical foreign key constraint might state that every employee in the EMPLOYEE table must be a member of an existing department, as defined in the DEPARTMENT table.
Referential integrity is the state of a database in which all values of all foreign keys are valid. A foreign key is a column or a set of columns in a table whose values are required to match at least one primary key or unique key value of a row in its parent table. A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is true, They appear as values of a parent key. Some component of the foreign key is null.
Introduction to the Db2 foreign key
Let’s take a look at the contacts and phones tables -
CREATE TABLE contacts( contact_id INT NOT NULL GENERATED ALWAYS AS IDENTITY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, PRIMARY KEY(contact_id) ); CREATE TABLE phones( phone_id INT NOT NULL GENERATED ALWAYS AS IDENTITY, phone_no VARCHAR(20) NOT NULL, phone_type VARCHAR(10) NOT NULL, contact_id INT NOT NULL, PRIMARY KEY(phone_id) );
In this diagram, each contact may have zero or many phones such as home phone, work phone, and emergency phone. However, each phone must belong to one and only one contact. The relationship between the contacts and phones is one-to-many.
For each row in the phones table, you can always find a corresponding row in the contacts table. But the current setup does not ensure this relationship. It means you can insert a new row into the phones table with the contact identification (contact_id) that does not exist in the contacts table.
Furthermore, if you delete a contact, all the phones of the deleted contact will remain in the phones table. The rows in the phones table that does not have corresponding rows in the contacts table are called orphaned rows. In order to enforce the relationship between contacts and phones tables, you need to use a foreign key constraint.
Definition of a Foreign Key
A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. When you create a database to manage information for a business, it is common for tables in the database to have columns in common. In the following illustration, the CUSTOMER and ORDER tables share the CustID column.
CustID is a primary key in the CUSTOMER table, CustID is a foreign key in the ORDER table. The difference between the CustID field in the CUSTOMER table and its counterpart in the ORDER table is that the CustID field is the primary-key field for the CUSTOMER table but not for the ORDER table. When table ORDER contains a field that is the primary-key field in table CUSTOMER, that field in table ORDER is referred to as a foreign key.
When a table contains a column (or concatenation of columns) that is the same as the primary key of a table, the column is called a foreign key. The matching of foreign key values to primary key values represents data relationships in a relational database. As far as the user of a relational database is concerned, there are no structures that show relationships other than the matching column's values.
If you have been using SQL or any database system for long, you know that one of the biggest advantages to a database is that, unlike a spreadsheet, it can draw data from a variety of different tables quite easily. However, to do this it must have an easy way to know which tables to draw data from. In short, it needs a key. A foreign key is a field that directly identifies another table. As you might imagine, this makes using foreign keys a very useful skill and one of the aspects of SQL that helps to set it apart from other programs.
Before we go any further, let's make sure that we understand the difference between a primary key and a foreign key. A primary key provides an absolute reference point to a record on a row. For example, ID numbers for employees are often primary keys. Meanwhile, a foreign key allows links to be made between different tables. A primary key may be the ID number on a main table of employees, but a name or an ID number would be a foreign key on the subsequent tables that describe other aspects of the employee.
A foreign key joins a table to another table by referencing its primary key. A foreign key constraint specifies that the key can only contain values that are in the referenced primary key, and thus ensures the referential integrity of data that is joined on the two keys. You can identify a table's foreign key when you create the table, or in an existing table with ALTER TABLE. For example, the following CREATE TABLE statement defines two foreign key constraints: fk_store_orders_store and fk_store_orders_vendor.
=> CREATE TABLE store.store_orders_fact(
product_key int NOT NULL,
product_version int NOT NULL,
store_key int NOT NULL CONSTRAINT fk_store_orders_store REFERENCES store.store_dimension (store_key),
vendor_key int NOT NULL CONSTRAINT fk_store_orders_vendor REFERENCES public.vendor_dimension (vendor_key),
employee_key int NOT NULL,
order_number int NOT NULL,
date_ordered date,
date_shipped date,
expected_delivery_date date,
date_delivered date,
quantity_ordered int,
quantity_delivered int,
shipper_name varchar(32),
unit_price int,
shipping_cost int,
total_order_cost int,
quantity_in_stock int,
reorder_level int,
overstock_ceiling int
);
The following ALTER TABLE statement adds foreign key constraint fk_store_orders_employee to the same table −
=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
FOREIGN KEY (employee_key) REFERENCES public.employee_dimension (employee_key);
The REFERENCES clause can omit the name of the referenced column if it is the same as the foreign key column name. For example, the following ALTER TABLE statement is equivalent to the one above −
=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
FOREIGN KEY (employee_key) REFERENCES public.employee_dimension;