Home / Questions / What is a foreign key of a database?
Explanatory Question

What is a foreign key of a database?

👁 531 Views
📘 Detailed Answer
🕒 Easy to Read
Read the answer carefully and go through the related questions on the right side to improve your understanding of this topic.

Answer with Explanation

To define the relationship between two tables (one is called parent and the other one is the child table) connected by columns, a foreign key constraint is used. In this constraint the values of the child table must appear in the parent table, which means that for a foreign key, one table should point to a Primary Key in another table. A table can have multiple foreign keys and each foreign key can have a different referenced table.

Example:

A foreign key in a relational database is a field or a set of fields that is used to establish a link or relationship between two tables. It creates a logical connection between data in different tables, allowing you to maintain referential integrity and enforce relationships between related records.

The primary purpose of a foreign key is to ensure data consistency and integrity across tables. It helps enforce the following rules:

  1. Referential Integrity: A foreign key ensures that values in a referencing (child) table correspond to values in a referenced (parent) table. This prevents the creation of orphaned records and maintains data accuracy.

  2. Relationships: Foreign keys establish relationships between tables, representing associations between entities in the database. For example, in a typical e-commerce scenario, a foreign key might link orders to customers.

Key characteristics of a foreign key:

  • References Primary Key: A foreign key column in one table references the primary key column(s) of another table.
  • Maintains Integrity: It ensures that values in the foreign key column(s) of the child table match existing values in the primary key column(s) of the parent table.
  • Cascade Actions: Optionally, you can define cascade actions that dictate what should happen when a referenced record is modified or deleted. Common cascade actions include cascading updates and cascading deletes.

Example: Consider two tables, "Orders" and "Customers." If you want to establish a relationship between these tables, you might create a foreign key in the "Orders" table that references the "CustomerID" primary key in the "Customers" table. This foreign key ensures that each order is associated with a valid customer.

SQL Syntax for creating a foreign key:


CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    -- Other columns
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, the "CustomerID" column in the "Orders" table is a foreign key that references the "CustomerID" primary key in the "Customers" table. This establishes a relationship between the two tables based on the customer ID.

Foreign keys play a crucial role in maintaining data integrity and enabling effective data modeling in relational databases.