Foreign Key ​

When we use a primary key to uniquely identify records, we can locate any student in the students table:

Similarly, we can identify any class record in the classes table:

To determine which class a student belongs to, we establish a one-to-many relationship by adding a class_id column to the students table, linking it to the classes table:

Here, class_id serves as a foreign key. This foreign key relationship is defined using a foreign key constraint:

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

This constraint ensures that if a class with id=99 does not exist in the classes table, a record with class_id=99 cannot be inserted into the students table.

While foreign key constraints enhance data integrity, many applications prioritize performance and may omit them, relying instead on application logic to maintain correctness.

To remove a foreign key constraint, use:

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

Note that this command removes the constraint but does not delete the class_id column.

Many-to-Many Relationships ​

In some cases, we need to define many-to-many relationships, such as when a teacher can belong to multiple classes, and a class can have multiple teachers. This is managed through an intermediary table that links two one-to-many relationships:

teachers table:

classes table:

Intermediate table (teacher_class):

This intermediary table allows us to define the many-to-many relationship between teachers and classes.

One-to-One Relationships ​

A one-to-one relationship occurs when a record in one table corresponds to a single record in another table. For example, if each student has a unique contact, this can be stored in a separate contacts table:

While it might seem efficient to add a mobile column to the students table, separating them can improve performance and maintain data integrity, especially when some students may not have a phone number.

Summary ​

Relational databases utilize foreign keys to implement one-to-many, many-to-many, and one-to-one relationships. Foreign keys can enforce constraints through the database or rely on application logic for validation.