Foreign Key in SQL

A foreign key is a column (or a set of columns) in one table (child table) that uniquely identifies a row in another table(parent table). Foreign keys play a critical role for establishing and maintaining relationships between tables. They enforce referential integrity, ensuring the consistency and accuracy of data relationships.

A foreign key establishes a link between two tables by referencing the primary key (or a unique key) in another table. The table that contains the primary key is called the parent table, and the table that contains the foreign key is known as the child table. In this article, we will explore all aspects of foreign keys. By the end, you will have a clear understanding of foreign keys, including their syntax, use cases and key concepts etc.

Syntax

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    FOREIGN KEY (Column1 ) REFERENCES ParentTableName(ParentColumnName)
);

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ColumnName) REFERENCES ParentTableName(ParentColumnName);

Q1. What is the purpose of a foreign key?

A foreign key ensures referential integrity between two tables by linking the child table to a primary or unique key in the parent table. It ensures that the value in the child column exists in the parent column.

Q2. Can a table have more than one foreign key?

Yes. A table can have multiple foreign keys, each referencing a different table or even different columns in the same table.

Q3. Can a foreign key reference a non-primary key column?

Q4. What happens if I try to insert a value in the child table that doesn’t exist in the parent table?

The insert will fail with a referential integrity constraint violation. The database will not allow orphan records that don’t have corresponding entries in the parent table.

Q5. How can I remove a foreign key constraint?

Use the ALTER TABLE statement with DROP CONSTRAINT

ALTER TABLE TableName
DROP FOREIGN KEY ConstraintName; -- MySQL

-- or

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;  -- SQL Server, PostgreSQL

Q6. Do foreign keys automatically create indexes?

It depends on the database system you are using. Here is how some major databases handle it

Q8. Can a foreign key reference multiple columns (composite key)?

Yes, A foreign key can reference composite keys, as long as the combination of columns in the parent table is a primary or unique key and the same number and type of columns are used in the child table.

Q9. Can foreign keys be self-referencing?

Yes, A table can have a self-referencing foreign key, where a column refers to another row in the same table. This is useful for hierarchies like employee-manager relationships.

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ManagerId INT,
    FOREIGN KEY (ManagerId) REFERENCES Employees(Id)
);

Q10. What is the difference between a primary key and a foreign key?

Q12. What is a circular foreign key dependency?

A circular foreign key dependency happens when two or more tables reference each other through foreign keys, either directly or indirectly.

This creates a loop in the relationships, making it tricky to insert, update or delete records because the operations depend on each other.

-- Table A references Table B
CREATE TABLE A (
  id INT PRIMARY KEY,
  b_id INT,
  FOREIGN KEY (b_id) REFERENCES B(id)
);

-- Table B references Table A
CREATE TABLE B (
  id INT PRIMARY KEY,
  a_id INT,
  FOREIGN KEY (a_id) REFERENCES A(id)
);

• You can't insert a row into A without a valid row in B.
• But you also can't insert into B without a valid row in A.
• This creates a deadlock unless you use special handling.

Q13. How to Handle Circular foreign key Dependencies

Circular foreign key dependencies can make insertions, updates, and deletions difficult because the tables rely on each other. To handle them safely and efficiently, you can use one or more of the following strategies

-- PostgreSQL Example
ALTER TABLE A
  ADD CONSTRAINT fk_b
  FOREIGN KEY (b_id)
  REFERENCES B(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Step 1: Insert A and B with NULL foreign keys
INSERT INTO A (id, b_id) VALUES (1, NULL);
INSERT INTO B (id, a_id) VALUES (10, 1);

-- Step 2: Update A with B's ID
UPDATE A SET b_id = 10 WHERE id = 1;

-- Example in MySQL
SET foreign_key_checks = 0;
-- perform inserts
SET foreign_key_checks = 1;

Q15. Can I disable foreign key checks temporarily?

Yes, in many (but not all) database systems you can temporarily disable foreign key enforcement , though the commands and support vary.

SET FOREIGN_KEY_CHECKS = 0;  -- Disable
-- Perform operations
SET FOREIGN_KEY_CHECKS = 1;  -- Re-enable

ALTER TABLE TableName NOCHECK CONSTRAINT ALL;
-- To re-enable:
ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ALL;

Q16. How can I find foreign key constraints on a table?

You can easily retrieve foreign key information using built-in system views or commands in major databases. Here are effective methods for each:

SELECT * FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'your_table_name' AND REFERENCED_TABLE_NAME IS NOT NULL;

Alternatively, simply run below query to view any defined foreign key constraints in the table creation statement.

SHOW CREATE TABLE your_table_name;

EXEC sp_fkeys 'your_table_name';

SELECT conname, confrelid::regclass AS parent_table
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass AND contype = 'f';

Q17. Can I update a referenced key in the parent table?

Yes, in some cases but it depends on how the foreign key constraint is defined.

• You can update a parent key only if it's defined with ON UPDATE CASCADE or there are no referencing rows.
• When ON UPDATE CASCADE is in effect, updating the parent table automatically updates matching foreign key values in child tables.
• If ON UPDATE CASCADE is not specified and there are existing child rows referencing that key, the update will fail with a referential integrity violation.
• Many databases (e.g., MySQL, SQL Server) by default use NO ACTION or RESTRICT for update rules, which block updates when dependent rows exist.

Q18. How does a foreign key affect DELETE operations?

By default, if a referenced row exists in the child table, deleting it in the parent table is blocked. You can control behavior using

• ON DELETE CASCADE – Deletes child rows automatically.
• ON DELETE SET NULL – Sets child foreign key column to NULL.
• ON DELETE RESTRICT or NO ACTION – Prevents deletion if related rows exist.

Q19. Can I create a foreign key on a computed or expression column?

No, Foreign keys must reference real, persistent columns, not derived or computed ones.

Q20. When should I avoid foreign keys?

While foreign keys are important for data integrity, you may avoid them in following cases

• High-throughput systems where constraint checks add overhead
• Data warehousing (ETL) environments where constraints are handled by logic.
• Applications where relationships are enforced at the application level rather than database level.