Foreign key constraint?

A foreign key constraint links a column in one table to the primary key (or a unique key) of another table, enforcing referential integrity so that a value in the child table must correspond to an existing value in the parent table.

Key Points: • It prevents "orphan" rows -- you can't insert a child row referencing a parent value that doesn't exist. • Databases can also cascade actions, such as ON DELETE CASCADE, to automatically delete or update related child rows when the parent changes. • Foreign keys are typically indexed to keep join performance and constraint checks efficient. • They document and enforce relationships directly in the schema, rather than relying on application code alone.

Example: In a school database, the Students table has a Course_ID column with a foreign key referencing the Courses table's Course_ID, which guarantees a student can never be assigned to a course that doesn't actually exist.

Code Example:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Interview Tip: A concise interview answer is:

"A foreign key constraint ties a column to the primary key of another table so the database enforces referential integrity -- you can't insert a child row pointing at a parent that doesn't exist. In a school database, a student's course_id would be a foreign key referencing the courses table, guaranteeing students are only ever assigned to real courses."