foreign key

What is a foreign key?

A foreign key is a column or columns of data in one table that refers to the unique data values -- often the primary key data -- in another table. Foreign keys link together two or more tables in a relational database.

To ensure the links between a foreign key and primary keys aren't broken, foreign key constraints are created to prevent actions that would damage the links between the two tables and prevent erroneous data from being added to the foreign key column.

Relational databases, such as Microsoft SQL Server, organize data points with defined relationships. They have several key types to help keep track of this information. Administrators can select or change a primary key as needed in a relational database.

Importance of foreign keys

Foreign keys are important for several reasons, including the following:

• Streamline data sets. With foreign keys, database administrators don't have to store repeated data in multiple tables. They make data available to different tables without creating redundant data sets. In other words, they act as a cross-referencing system among tables.
• Promote efficiency. Primary and foreign keys work together to create structure in relational databases, enabling them to sort, search and query data faster.
• Ensure data integrity. Primary and foreign key relationships also help maintain the data integrity of relational databases. They ensure the existence of a value in the primary table and the accuracy of the foreign key reference in the primary table, even if the primary table is changed or deleted.

Example of how foreign keys work

Figure 2 shows two tables that contain basic data about customers and their orders from an online office furniture vendor.

The primary key in the Customers table is CustomerNo, and the primary key in the Orders table is OrderNo. The primary key uniquely identifies the table and contains the value that the foreign key refers to. Primary keys must be unique to each row of data. In the Orders table, the CustomerNo value is a foreign key that links the two tables relating back to the CustomerNo value in the Customers table.

When a primary key in a table changes, the set of associated foreign keys also changes.

A primary key column in the original or parent table can be targeted by multiple foreign keys from other tables in the database, which are called child tables. But a primary key does not have to be the target of foreign keys.

Differences between primary keys and foreign keys

Primary and foreign keys commonly work together to link tables in a relational database. They each do something different to characterize and link tables.

Primary keys

A database table has only one primary key. A table's primary key acts as a unique identifier. The primary key values identify specific records and prevent duplication of records. Null values are not permitted in a primary key.

Primary keys generally should be read-only because they must be consistent to maintain the unique identity of the table. The following are some real-world primary key data types:

• driver's license numbers
• telephone numbers
• customer ID numbers
• Social Security numbers
• vehicle identification numbers

Foreign keys

These link two tables together. It is possible to have more than one foreign key in a table, and they can accept a null value. Foreign key values do not need to be unique; duplicate values can be stored in foreign key columns.

Foreign keys do have to link back to columns with unique values. Those columns are frequently primary keys.

What is a foreign key constraint?

Constraints in general are pieces of code that enforce rules and regulations on the data. They create conditions that the data must satisfy.

Foreign key constraints prevent invalid data from being placed into the foreign key column because the data must be one of the values contained in the table where it's directed. A foreign key constraint links a column in one table to a column in another and prevents actions that would destroy the link between two tables.

In the context of the above example, the constraint is a bit of code that ensures a value can only be added to the Orders table if it already exists in the Customers table.

SQL is a coding language used for programming relational databases. To create a constraint to define a foreign key in Figure 2, the following SQL code would be used:

ALTER TABLE ORDERS
ADD FOREIGN KEY (CustomerNo) REFERENCES CUSTOMERS (CustomerNo);

Any constraint defined using the above ALTER TABLE command can be dropped using the DROP CONSTRAINT command. To drop or remove a foreign key, use the following SQL syntax:

ALTER TABLE ORDERS 
DROP FOREIGN KEY;

Types of constraints

Foreign keys do not have to be linked specifically to a primary key constraint in another table; they can also reference the columns of a UNIQUE constraint elsewhere.

Foreign key constrains are not the only types of constraints. The following are some other SQL constraint types:

• CHECK constraints limit the values that are accepted by more than one column.
• DEFAULT constraints apply when a null value isn't appropriate. Default values are added to records when no other value is specified. For example, an administrator can set zero as a default value to appear in a column when no other value is specified.
• UNIQUE constraints ensure there are no duplicate values entered in specific columns that are not within a primary key.
• NOT NULL constraints ensure that a column always contains a value.
• PRIMARY constraints uniquely identify each row in a table.

Foreign key problems

Many database users encounter foreign key errors, often because of referential integrity problems. Referential integrity is the accuracy and consistency of data in a relationship. A foreign key might point to data that no longer exists, or the foreign key's data type doesn't match the primary key data type, eroding referential integrity.

Referential integrity can also be corrupted if the foreign key doesn't reference all the data from the primary key. If there's a parent table for Sales which consists of a primary key of the company name, department name and address, then the child table for Customers must refer to all attributes of the parent table -- not just one or two. If the child table presents a foreign key value that doesn't correspond to a value in the parent table, it corrupts referential integrity. The unmatched value in the child table is sometimes referred to as an orphan record.

In Figure 2, if the CustomerNo column in the Orders table contained the value five, that value would be an orphan because there is no corresponding CustomerNo 5 in the parent table. If a primary key was deleted from the Customers table, referential integrity would be damaged.

Database administrators can define how the database engine handles foreign key problems and lapses in referential integrity using referential integrity constraints. If a user tries to delete or update a key that a foreign key points to, the following user-defined constraints dictate the referential actions the database engine can take in response:

• NO ACTION. The database engine raises an error and the action is undone.
• CASCADE. Corresponding rows are updated or deleted in the table with a foreign key when the parent table's corresponding row is deleted or updated.
• SET NULL. When enabled, SET NULL allows null values in a table originating from ALTER TABLE or CREATE TABLE.
• SET DEFAULT. The referenced table sets its foreign key value to the default value upon the deletion or updating of the primary table.

Cloud-based relational databases use foreign keys to give them structure and to maintain data integrity. Learn how cloud databases are deployed and managed and the benefits of using them.