Bert Swope
In a database, a composite primary key is a primary key that consists of two or more columns. A primary key is a unique identifier for a record in a table, and it is used to ensure that each record in the table can be uniquely identified.
A composite primary key is used when a single column cannot uniquely identify a record in the table, but a combination of columns can. For example, let’s say we have a table of student course registrations where each student can register for multiple courses. A single column, such as student ID, may not be enough to uniquely identify each record in the table, as a student can register for the same course multiple times. In this case, a composite primary key consisting of both student ID and course ID can be used to ensure that each registration record is uniquely identified.
The syntax for creating a composite primary key in SQL is as follows:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, …);
);
In the above example, pk_constraint_name is the name of the primary key constraint, and (column1, column2, …) specifies the columns that make up the composite primary key.
Like a Primary Key, what a composite Primary Key is depends on the database. Essentially a Composite Primary Key:
- Is a combination of Fields (columns) that uniquely identifies every row.
- Is an index in database systems that use indexes for optimization
- Is a type of table constraint
- It is applied with a data definition language (DDL) alter command
- And may define parent-child relationship between tables