Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →A composite primary key uses two or more columns together to identify each row uniquely. The full combination cannot be repeated, and every column in the key must contain a non-null value; an individual value may recur when paired with a different value.
How a composite primary key identifies rows
PostgreSQL describes a primary key as a column or group of columns that can uniquely identify table rows. With a composite key, uniqueness applies to the group—not to each column on its own. For example, in a product-vendor table, a product can appear with several vendors and a vendor can supply several products, but the same product-vendor pair cannot appear twice. PostgreSQL documentation and Microsoft’s SQL Server documentation describe these primary-key rules and examples.
As an Amazon Associate I earn from qualifying purchases.
Example: a student-course enrollment table
If each student may enroll in many courses and each course may have many students, the pair of student and course IDs can identify each enrollment:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →CREATE TABLE enrollment (
student_id integer NOT NULL,
course_id integer NOT NULL,
enrolled_on date,
PRIMARY KEY (student_id, course_id)
);This permits rows such as (12, 101) and (12, 205), because the student is enrolled in different courses. It also permits (27, 101), because a different student is taking course 101. It rejects another row with (12, 101), since that complete pair already identifies an enrollment. Neither student_id nor course_id has to be unique by itself.
#1 Best Overall
How to declare the key in SQL
For a multi-column primary key, declare one table-level constraint listing all participating columns:
PRIMARY KEY (student_id, course_id)This is one primary key made from both columns, not two separate primary keys. A table can have at most one primary key constraint, though it can have other unique constraints for additional uniqueness rules. A primary key also names the table’s designated row identifier and can serve as the default set of referenced columns when another table declares a foreign key to it. See PostgreSQL’s primary-key documentation and its foreign-key documentation.
What the database enforces
- Every complete key combination is unique. The database rejects a second row with the same values across all key columns.
- Every key column is non-null. A primary-key component cannot be omitted or set to
NULL. PostgreSQL documents that declaring a primary key marks its participating columnsNOT NULL. - The key is indexed for enforcement and lookup. PostgreSQL creates a unique B-tree index on the primary-key columns. Other database products may use different implementations or impose different limits; consult the documentation for the product and version in use.
These properties are described in PostgreSQL 16’s primary-key documentation.
Recommended Free Tools
When a composite key fits—and what to weigh
A composite key is a natural fit when the combination itself represents the row’s identity, such as a relationship table where each student-course or product-vendor pairing should occur only once. The enrollment example makes that rule explicit: the database prevents duplicate enrollments by preventing a repeated pair.
Rank #3
Before choosing one, consider whether the component values are stable and meaningful, how much key data referencing tables must carry in their foreign keys, and whether your database and application stack handle multi-column keys conveniently. Also check product-specific index and key-size restrictions. There is no universal rule that a composite key is better or worse than a single-column surrogate key; the choice depends on what defines a row and the practical requirements of the system.
For scale, Microsoft documents a SQL Server primary-key limit of 32 columns and 900 bytes of total key length. Those are SQL Server-specific limits, not general limits for all SQL databases; verify the documentation for the exact product and version you use. Microsoft’s SQL Server capacity specifications provide the product details.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Composite primary key versus a unique constraint
A primary key is the table’s designated row identifier and requires unique, non-null key values. A unique constraint can enforce a different candidate identifier—such as a username or external reference—without making it the primary key. A table may have multiple unique constraints but only one primary key. Because database products can differ in how unique constraints treat NULL, check the relevant product documentation before relying on null behavior.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
