SQL Tutorial

✨ SQL FOREIGN KEY Constraint

A FOREIGN KEY in SQL is a column or a group of columns in a table that uniquely identifies a row of another table. It creates a relationship between the data in two tables. The FOREIGN KEY constraint ensures the referential integrity of the data by making sure that a value in the foreign key column matches a primary key in another table.

1️⃣ Creating a Table with FOREIGN KEY

A FOREIGN KEY constraint is used to enforce a link between two tables. Here's how you can define a foreign key during table creation:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

CREATE TABLE posts (
  post_id INT PRIMARY KEY,
  user_id INT,
  post_content TEXT,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

In this example, the posts table has a user_id column that links to the user_id column in the users table using the FOREIGN KEY constraint. This ensures that each post is associated with a valid user.

2️⃣ Inserting Data with FOREIGN KEY

When you insert data into a table with a foreign key, the database ensures that the foreign key values match the primary key values in the referenced table. Here's an example of inserting data:

-- Valid Insert (user_id 1 exists in the users table)
INSERT INTO posts (post_id, user_id, post_content)
VALUES (1, 1, 'This is a valid post.');

-- Invalid Insert (user_id 99 does not exist in the users table)
INSERT INTO posts (post_id, user_id, post_content)
VALUES (2, 99, 'This will fail because user_id 99 does not exist in the users table.');

The second insert will fail because the user_id "99" does not exist in the users table. The FOREIGN KEY constraint ensures referential integrity.

3️⃣ Altering a Table to Add a FOREIGN KEY

If you have an existing table and want to add a foreign key, you can use the ALTER TABLE statement:

ALTER TABLE posts
ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

This will add the FOREIGN KEY constraint on the user_id column, establishing the relationship with the users table.

4️⃣ Handling Foreign Key Violations

If you attempt to delete or update a record in the referenced table (e.g., users) that is still being referenced in the child table (e.g., posts), a foreign key violation will occur unless specific actions are defined.

CREATE TABLE posts (
  post_id INT PRIMARY KEY,
  user_id INT,
  post_content TEXT,
  FOREIGN KEY (user_id) REFERENCES users(user_id) 
  ON DELETE CASCADE
);

In this case, when a record in the users table is deleted, all related posts in the posts table will also be deleted automatically due to the ON DELETE CASCADE rule.

🔧 Key Considerations

• Data Integrity: The FOREIGN KEY constraint ensures that you only reference valid rows in another table.
• Referential Actions: You can define actions like CASCADE, SET NULL, or NO ACTION to handle foreign key violations.
• Performance: While foreign keys ensure data integrity, they can have performance impacts on large datasets, especially on DELETE or UPDATE operations.