10 Understanding Primary Keys and Foreign Keys

In relational databases, primary keys and foreign keys are used to establish relationships between tables. A primary key is a unique identifier for a record in a table, while a foreign key is a field that refers to the primary key of another table.

Primary Keys

A primary key is a column or set of columns in a table that uniquely identifies each row in that table. It must have a unique value for each record and cannot be null. Primary keys are used to ensure data integrity and provide a way to access records in the table quickly. They are often used as the basis for creating relationships with other tables.

Let's create a table called "students" with a primary key:

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10)
);

In this example, we have created a table called "students" with an "id" column as the primary key. This column will contain a unique value for each record in the table.

Foreign Keys

A foreign key is a field in a table that refers to the primary key of another table. It is used to establish a relationship between the two tables. The foreign key in one table is the primary key in another table.

Let's create a new table called "courses" and establish a relationship with the "students" table using a foreign key:

CREATE TABLE courses (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  teacher VARCHAR(50),
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

In this example, we have created a table called "courses" with a "student_id" column as the foreign key. This column refers to the "id" column in the "students" table. This relationship ensures that any record in the "courses" table with a "student_id" value that does not exist in the "students" table will not be allowed.

Conclusion

Primary keys and foreign keys are essential elements in relational databases. They are used to establish relationships between tables, ensure data integrity, and provide a way to access records quickly. Understanding how to use primary keys and foreign keys is crucial for creating effective database designs and queries.