how to add foreign key in sql

To add a foreign key in SQL, you can do so either when creating a new table or by altering an existing table. A foreign key is a column (or set of columns) in one table that references the primary key in another table, establishing a link between the two tables and enforcing referential integrity.

Adding a Foreign Key When Creating a Table

Use the CREATE TABLE statement with the FOREIGN KEY constraint:

sql

CREATE TABLE ChildTable (
    id INT PRIMARY KEY,
    parent_id INT,
    CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES ParentTable(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• parent_id is the foreign key column in ChildTable.
• It references the id column in ParentTable.
• ON DELETE CASCADE and ON UPDATE CASCADE specify that changes in the parent table propagate to the child table automatically

Adding a Foreign Key to an Existing Table

If the table and the foreign key column already exist, use the ALTER TABLE statement:

sql

ALTER TABLE ChildTable
ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id)
REFERENCES ParentTable(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

• This adds a foreign key constraint named fk_parent on the parent_id column.
• The referenced table and column must exist and have compatible data types.
• The ON DELETE and ON UPDATE clauses define behavior on changes in the referenced table

If the foreign key column does not exist yet, first add it:

sql

ALTER TABLE ChildTable
ADD parent_id INT;

Then add the foreign key constraint as above

Notes

• The foreign key column(s) should be indexed for performance.
• You can add foreign keys on multiple columns by listing them comma-separated.
• Different SQL dialects (MySQL, SQL Server, PostgreSQL) support similar syntax with minor variations

This approach ensures data integrity by linking related tables and preventing invalid references.