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
Here's how you'd structure the SQL command:
CREATE TABLE students (
student_id INT NOT NULL,
PRIMARY KEY (student_id)
student_id INT NOT NULL: This defines a column named
student_idthat stores integer values and cannot be left empty (
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_idcolumn as the primary key for the
studentstable, 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
CREATE TABLE enrollments (
enrollment_id INT NOT NULL,
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
enrollmentstable that will store the Student IDs from the
FOREIGN KEY (student_id) REFERENCES Students(student_id): This is the essential part. It designates the
student_idcolumn in the
enrollmentstable as a foreign key that references the
student_idcolumn in the
studentstable. This ensures that every
student_identered in the
enrollmentstable matches a
student_idthat already exists in the
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.
- 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 KEYkeyword is used, followed by the column that will act as the foreign key, and then the
REFERENCESkeyword 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.