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.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
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
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
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
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
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.