What is a foreign key of a database?

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.