SQL FOREIGN KEY Constraint

A FOREIGN KEY constraint is a concept in SQL that enforces a valid relationship between two tables by ensuring that the values stored in the child table correspond to existing values in the parent table. This constraint protects the database from inconsistent or invalid relational data.

• Ensures child table values match valid primary keys in parent table.
• Blocks actions that create broken or unmatched table references.
• Uses CASCADE or RESTRICT rules to control related data changes.
• Keeps table relationships consistent by enforcing referential integrity rules.

Query:

CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(20),
    address VARCHAR(20), age INT, dob DATE
);

CREATE TABLE courses ( course_name VARCHAR(20), instructor VARCHAR(20), reference_id INT,
 CONSTRAINT fk_refer  FOREIGN KEY (reference_id) REFERENCES student(student_id)
);

 -- Insert into student table
INSERT INTO student VALUES (1, 'Michael', 'New York', 20, '2005-03-10');
INSERT INTO student VALUES (2, 'Emma', 'London', 22, '2003-07-21');

-- Insert into courses table
INSERT INTO courses VALUES ('mathematics', 'Dr. Smith', 1);
INSERT INTO courses VALUES ('biology', 'Dr. Adams', 2);

Output:

student Table

courses Table

• reference_id in courses references student_id in student, creating a valid link.
• The FOREIGN KEY ensures only existing student IDs can be used in the courses table.
• This maintains proper referential integrity and prevents invalid references.

Syntax:

The syntax to create a foreign key in CREATE TABLE statement is:

CREATE TABLE table_name (  
  column1 datatype,  
  column2 datatype,
  . . . ,
  CONSTRAINT fk_constraint_name 
    FOREIGN KEY (column1, column2, ...)
    REFERENCES parent_table(column1, column2, ...)
);

The syntax to add a foreign key with ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name
   FOREIGN KEY (column1, column2, ...)
   REFERENCES parent_table(column1, column2, ...);

Foreign Key Constraint Examples

In these examples, we use the student and courses tables created earlier, where the foreign key concept was implemented.

Example 1: Insert Value in Foreign Key Table

If a corresponding value in the foreign table doesn't exist, a record in the child table cannot be inserted.

Query:

INSERT INTO courses (course_name, instructor, reference_id)
VALUES ('physics', 'Dr. Green', 5);

Error:

• reference_id = 5 doesn’t exist in the student table.
• The insert fails because it violates the foreign key constraint.

Example 2: Delete a value in Foreign Key Table

When a record in the master table is deleted and the corresponding record in the child table exists, an error message is displayed and prevents the DELETE operation from going through.

Query:

DELETE FROM student
WHERE student_id = 1;

Error:

• student_id = 1 is already referenced in the courses table.
• The delete fails because removing it would break the foreign key relationship.

Create Quiz