A foreign key is a column in one table whose values are required to exist in another table’s Primary key (or, less commonly, in another table’s unique key). It’s how we point from one row in one table to a row in another.
If Students has a major_code column meant to identify each student’s major, and Majors has a major_code primary key, declaring Students.major_code as a foreign key referencing Majors.major_code says: the value in Students.major_code must always match some real major. The database engine enforces this — it won’t let us write a value into Students.major_code unless the value exists in Majors.
ALTER TABLE students
ADD FOREIGN KEY (major_code) REFERENCES majors(major_code)
ON DELETE CASCADE;The ON DELETE CASCADE part says: if a major is deleted from Majors, also delete every student in Students that pointed to that major. See ON DELETE CASCADE for the alternatives (RESTRICT, SET NULL, NO ACTION) and the reasoning around them.
Foreign keys are how the database guarantees referential integrity — every relationship written in one table can always be followed to a real row in the other table. Without foreign keys, we could end up with a student whose major_code is 47 even though no major with code 47 exists. With them, the database refuses to create such a student in the first place.
Foreign keys can be given names, useful when we later want to refer to them — for example, to drop them:
ALTER TABLE students
ADD CONSTRAINT fk_students_major_code
FOREIGN KEY (major_code) REFERENCES majors(major_code);
ALTER TABLE students
DROP FOREIGN KEY fk_students_major_code;A foreign key is one of several SQL constraints, alongside NOT NULL, UNIQUE, CHECK, and PRIMARY KEY.
The DROP FOREIGN KEY syntax above is MySQL-specific. In standard SQL — and in PostgreSQL, SQL Server, and SQLite — the corresponding statement is ALTER TABLE students DROP CONSTRAINT fk_students_major_code.