What is fk in sql

What is a Foreign Key?

A Foreign Key (FK) is a database constraint that creates a link between two tables. It references the primary key of another table, establishing a parent-child relationship. This ensures that data in one table matches with data in another table, maintaining database integrity.

How Foreign Keys Work

When you define a Foreign Key on a column, the database enforces that every value in that column must either be NULL or match an existing primary key value in the referenced table. For example, if a "Orders" table has a Foreign Key referencing the "Customers" table's primary key, every order must be linked to a valid customer.

Benefits of Using Foreign Keys

• Referential Integrity: Prevents orphaned records where a child record references a non-existent parent
• Data Consistency: Ensures relationships between tables remain valid
• Cascading Actions: Automatically handles updates or deletions across related tables
• Query Optimization: The database can optimize joins using foreign key relationships

Foreign Key Constraints

Foreign Keys support several constraint actions: RESTRICT prevents deletion of referenced rows, CASCADE automatically deletes child rows, SET NULL sets the foreign key to NULL when the parent is deleted, and NO ACTION is similar to RESTRICT but checked at transaction end. These actions determine how the database handles changes to parent records.

Creating Foreign Keys in SQL

You define a Foreign Key using the ALTER TABLE or CREATE TABLE statement. The syntax typically includes FOREIGN KEY (column_name) REFERENCES parent_table(primary_key). Most modern databases support Foreign Keys, including MySQL, PostgreSQL, SQL Server, and Oracle, making them a standard feature for maintaining database integrity.