A foreign key is a column or set of columns in a relational database table that refers to the primary key of another table. A foreign key establishes a relationship between two tables, where the table containing the foreign key is the child table and the table containing the primary key is the parent table.
Here are some key characteristics of foreign keys:
Referential integrity: A foreign key ensures referential integrity between two tables. This means that data in the child table is consistent with data in the parent table.
Uniqueness: A foreign key value must exist in the parent table, which ensures that each record in the child table refers to a valid record in the parent table.
Cascading updates and deletes: A foreign key can be configured to cascade updates and deletes to maintain referential integrity. This means that when a record in the parent table is updated or deleted, the corresponding records in the child table are also updated or deleted.
Here is an example of a foreign key definition in a SQL CREATE TABLE statement:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);In this example, the orders table contains a foreign key customer_id that refers to the customer_id column in the customers table. This establishes a relationship between the orders and customers tables, where each order is associated with a customer.
Foreign keys help maintain referential integrity in a database by ensuring that data in related tables is consistent and valid. By enforcing referential integrity, foreign keys prevent orphaned records and ensure that data is accurate and consistent across tables. Foreign keys also simplify data retrieval and analysis by providing a way to join related tables together.
Overall, foreign keys are an important aspect of relational database design and play a critical role in ensuring data accuracy and consistency.