how to add foreign key in sql

To add a foreign key in SQL, you define a relationship between a column (or columns) in one table and the primary key (or unique key) of another table.

Core idea in plain terms

A foreign key is a constraint that makes sure values in one table must exist in another table’s key column, keeping your data consistent and preventing “orphan” rows.

Basic syntax (during CREATE TABLE)

You can add a foreign key directly when creating a table:

sql

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    order_date   DATE,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

• fk_orders_customer is the constraint name (you choose it).

• customer_id in orders must match an existing customer_id in customers.

Add a foreign key to an existing table (ALTER TABLE)

If the table already exists, you typically use ALTER TABLE ... ADD CONSTRAINT.

Generic pattern

sql

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
    FOREIGN KEY (child_column)
    REFERENCES parent_table(parent_column);

Example:

sql

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id);

• orders is the child table.
• customers is the parent table with a primary key customer_id.

Step‑by‑step mini‑guide

1. Check parent table key exists
   Make sure the referenced column is a primary key or unique key.

2. Ensure data is consistent
   Existing values in the child column must all be present in the parent key column, or the ALTER TABLE will fail.

3. Add the constraint
   Use ALTER TABLE as shown above.

4. Optional: ON DELETE / ON UPDATE rules

   sql
   
   ALTER TABLE orders
   ADD CONSTRAINT fk_orders_customer
       FOREIGN KEY (customer_id)
       REFERENCES customers(customer_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE;
   

   • ON DELETE CASCADE: deleting a customer deletes their orders.

 * `ON DELETE RESTRICT` or `NO ACTION`: prevent deleting if related rows exist.

Example with two tables (story‑style walkthrough)

Imagine you have Department and Student tables.

1. Create the parent table with a primary key:

   sql
   
   CREATE TABLE Department (
       id   INT PRIMARY KEY,
       name VARCHAR(100)
   );
   

2. Create or modify the child table:

   sql
   
   CREATE TABLE Student (
       id            INT PRIMARY KEY,
       name          VARCHAR(100),
       department_id INT
   );
   

3. Add the foreign key:

   sql
   
   ALTER TABLE Student
   ADD CONSTRAINT fk_student_department
       FOREIGN KEY (department_id)
       REFERENCES Department(id);
   

Now, every Student.department_id must match a valid Department.id or be NULL (if you allow NULLs).

Multi‑column (composite) foreign keys

You can reference multiple columns if the parent has a composite key.

sql

ALTER TABLE Course
ADD CONSTRAINT fk_course_department
    FOREIGN KEY (department_id, name)
    REFERENCES Department (id, name);

Both (department_id, name) pairs must exist in Department (id, name).

Common errors and how to avoid them

• “Violation of foreign key constraint” on insert
  You’re inserting a child row whose key doesn’t exist in the parent table.

* Fix: Insert the parent first, or correct the foreign key value.

• Cannot create constraint because of existing data
  Existing rows in the child table have values not present in the parent key.

* Fix: Clean or fix the data, then re‑run the `ALTER TABLE`.

• Wrong datatype or length
  The child column must match the parent column’s datatype (and usually size and collation).

Mini HTML table recap

[3] [5][1] [10][1] [1][3]

Task	SQL snippet
Create FK in new table	CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col)
Add FK to existing table	ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col);
With cascade	... REFERENCES parent(col) ON DELETE CASCADE ON UPDATE CASCADE;
Composite foreign key	FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2)

Quick SEO elements

• Focus phrase: how to add foreign key in sql appears in title and key sections.

• Meta description suggestion:

Learn how to add a foreign key in SQL using CREATE TABLE and ALTER TABLE, with examples, cascade rules, and tips to avoid common foreign key errors.

TL;DR: Use CREATE TABLE ... CONSTRAINT ... FOREIGN KEY ... REFERENCES ... for new tables, and ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES ... to add a foreign key to existing tables, ensuring matching data and compatible key definitions first.

Information gathered from public forums or data available on the internet and portrayed here.