Foreign Keys

Relational databases organize data into tables, and sometimes, these tables need to share information. For this shared information to make sense and be reliable, we need to establish relationships between the tables.

The question is, how do we establish a relationship between tables? The primary mechanism to establish these relationships is through keys:

• Primary Key: A column (or set of columns) in a table where each value is unique. It uniquely identifies each record in that table.
• Foreign Key: A column in one table that contains values from the primary key column of another table. It establishes a link between the two tables.

By using a foreign key, one table refers to the primary key in another table. This ensures that the data in the table with the foreign key corresponds to the data in the table it refers to. The main purpose of foreign keys is to maintain data integrity and to ensure that the relationships between tables remain consistent.

Creating a Primary Key

To create a table with a primary key in SQL, you'd use the CREATE TABLE statement along with the PRIMARY KEY keyword.

Here's a basic example:

Let's say you want to create a table named students with a unique identifier for each student called student_id. The table would also have columns for first_name and last_name.

Here's how you'd structure the SQL command:

CREATE TABLE students (
  student_id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  PRIMARY KEY (student_id)
);

• student_id INT NOT NULL: This defines a column named student_id that stores integer values and cannot be left empty (NULL).
• first_name VARCHAR(50) and last_name VARCHAR(50): These define columns for storing first and last names as strings with a maximum length of 50 characters.
• PRIMARY KEY (student_id): This designates the student_id column as the primary key for the students table, ensuring that each value in this column is unique.

When you have this table set up, each student you add must have a unique student_id. If you tried to add two students with the same student_id, the database would give you an error, ensuring the integrity of your data.

Creating a Foreign Key

Foreign keys can be created by using the FOREIGN KEY keywords followed by the name of the column with the column name from another table wrapped in parentheses.

To illustrate using a foreign key with our students example, let's imagine another table called enrollments, which will store information about the courses each student is enrolled in.

In this new table, we would want to have an identifier for the course, say course_id, and a reference to the student who is enrolled in that course. This reference to the student is where the foreign key comes in. We'll use student_id from the students table as a foreign key in the enrollments table.

CREATE TABLE enrollments (
  enrollment_id INT NOT NULL,
  course_id INT,
  student_id INT,
  PRIMARY KEY (enrollment_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

In this example:

• enrollment_id INT NOT NULL: This defines a unique identifier for each enrollment.
• course_id INT: This defines an identifier for the course in which the student is enrolled.
• student_id INT: This is the column in the enrollments table that will store the Student IDs from the students table.
• FOREIGN KEY (student_id) REFERENCES Students(student_id): This is the essential part. It designates the student_id column in the enrollments table as a foreign key that references the student_id column in the students table. This ensures that every student_id entered in the enrollments table matches a student_id that already exists in the students table.

In practice, this foreign key relationship ensures that you can't enroll a non-existent student in a course. If you tried to insert an enrollment with a student_id that doesn't exist in the students table, the database would prevent it to maintain data integrity.

Key Takeaways

• Relational databases organize data into tables that can be interlinked or related to one another.
• A foreign key column in one table that establishes a link to the primary key in another table. It ensures relationships between tables are consistent and data integrity is maintained.
• Foreign keys ensure that the data in one table corresponds to the data in a related table.
• When defining a table, the FOREIGN KEY keyword is used, followed by the column that will act as the foreign key, and then the REFERENCES keyword followed by the table and column it references.
• The use of foreign keys enforces data integrity. If you try to insert or update data in a way that would break the relationship, the database will prevent it.