FOREIGN KEY and Relationships

Definition: A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. It is the tool used to link two tables together and establish a relationship between them.

Learning Outcome: Establishing table relationships is a vital SQL skill. In a relational database, data is split into multiple tables to avoid redundancy. Foreign keys ensure "Referential Integrity," meaning the database will prevent you from creating a link to a record that doesn't exist.

Example: Linking Courses and Enrollments

In this example, we have a courses table and an enrollments table. The enrollments table uses a foreign key to point back to the specific course a student is taking.

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

CREATE TABLE enrollments (
    id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

How It Works

• Parent Table: The courses table is the "Parent." It holds the master list of all available courses.
• Child Table: The enrollments table is the "Child." It "borrows" the course_id from the parent table to show which course a student is enrolled in.
• The Constraint: Because of the FOREIGN KEY, you cannot add an enrollment for a course_id that does not exist in the courses table.

Types of Relationships

Key Notes

• Data Integrity: Foreign keys prevent "orphan records." For instance, the database can be set to prevent you from deleting a course if there are still students enrolled in it.
• Indexing: Most databases automatically create an index on foreign keys, which makes searching for related data across tables much faster.
• Naming Convention: It is a common professional practice to name the foreign key column the same as the primary key it references (e.g., course_id references course_id).