Composite Primary Keys in SQL

A primary key is a column or set of columns that uniquely identifies each row in a database table. This means that no two rows in the table can have the same values in the primary key columns. Primary keys are used to enforce data integrity and prevent duplicate records from being inserted into the table.

Composite primary keys are useful

Composite primary keys can be useful in cases where a single column is not sufficient to uniquely identify each row in a table. For example, in a table of students, a single student_id column may not be enough to ensure that each row is unique, since multiple students may have the same student_id. In this case, a composite primary key consisting of both the class_id and student_id columns can be used to uniquely identify each student in a specific class.

Adding a composite primary key when you create a table

One way to create a composite primary key is to specify the columns that will be part of the key when the table is created. For example, the following SQL statement creates a table called students with a composite primary key consisting of the class_id and student_id columns:

CREATE TABLE students (
  class_id INT NOT NULL,
  student_id INT NOT NULL,
  name VARCHAR(255),
  PRIMARY KEY (class_id, student_id)
);

Adding a composite primary key to an existing table

Another way to create a composite primary key is to use the ALTER TABLE statement to add a composite primary key to an existing table. For example, the following SQL statement adds a composite primary key to the students table:

ALTER TABLE students
ADD PRIMARY KEY (class_id, student_id);

Using the composite primary key

Once a composite primary key has been created, it can be used like any other primary key. For example, the following SQL statement inserts a new row into the students table, using the composite primary key to ensure that the row is unique:

INSERT INTO students (student_id, class_id, name)
VALUES (1, 1, 'John Doe');

In addition to creating and using composite primary keys, it is also possible to modify them using the ALTER TABLE statement. For example, the following SQL statement removes the composite primary key from the students table:

Dropping the composite primary key

ALTER TABLE students
DROP PRIMARY KEY;

Composite primary key summary

In summary, composite primary keys are a type of database key that consists of two or more columns in a table. They can be created when the table is created, or added to an existing table using the ALTER TABLE statement. Once created, composite primary keys can be used to enforce data integrity and prevent duplicate records from being inserted into the table.