Schema & Design
PRIMARY KEY Constraints
A PRIMARY KEY constraint uniquely identifies each row in a table.
Overview
A PRIMARY KEY constraint uniquely identifies each row in a table. It combines two properties: uniqueness and non-nullability. A table can have at most one primary key, though that key may consist of multiple columns (a composite key). The constraint is defined at table creation or added later with ALTER TABLE. Primary keys are essential for data integrity, enabling reliable row identification and supporting relationships via foreign keys. In standard SQL, a primary key implies a unique constraint and a NOT NULL constraint on its columns. Most database systems automatically create a unique index to enforce the primary key, which also speeds up lookups. The SQL standard introduced PRIMARY KEY in SQL-86, and it remains a core feature across all major relational databases. While syntax is largely consistent, minor variations exist in constraint naming and deferrability. Understanding primary keys is fundamental to schema design and normalization.
Syntax and usage
The PRIMARY KEY constraint can be specified inline with a column definition or as a separate table-level clause. Inline syntax: column_name data_type PRIMARY KEY. Table-level syntax: PRIMARY KEY (column1, column2). You can name the constraint using CONSTRAINT constraint_name PRIMARY KEY (...). For composite keys, the table-level form is required. To add a primary key to an existing table: ALTER TABLE table_name ADD PRIMARY KEY (column1, column2);. To drop it: ALTER TABLE table_name DROP PRIMARY KEY; (syntax varies: some systems use DROP CONSTRAINT constraint_name). In standard SQL, primary key columns are implicitly NOT NULL. Some vendors allow specifying NOT NULL explicitly, but it is redundant. Deferrable constraints are supported in systems like PostgreSQL and Oracle, allowing checks to be postponed until transaction commit. MySQL, for example, uses AUTO_INCREMENT for surrogate keys, while SQL Server uses IDENTITY. These are vendor extensions, not part of the standard.
How it works
When a PRIMARY KEY constraint is defined, the database enforces two rules: all values in the key columns must be unique, and none may be NULL. Enforcement occurs on INSERT and UPDATE statements. If a duplicate or NULL value is inserted, the statement fails with a constraint violation error. Internally, most systems implement the primary key by creating a unique index on the key columns. This index not only enforces uniqueness but also provides fast access paths for queries that filter or join on the key. For composite keys, the index is on the combination of columns, and uniqueness applies to the tuple. The primary key also serves as the target for foreign key references, ensuring referential integrity. In some databases, the primary key index is clustered, meaning the table's rows are physically ordered by the key; in others, it is non-clustered. The choice affects performance but not semantics. The constraint is checked immediately unless declared DEFERRABLE, in which case validation may be delayed until transaction end.
Examples
Create a table with a single-column primary key: CREATE TABLE employees (employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50));. For a composite key: CREATE TABLE order_items (order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id));. Add a primary key to an existing table: ALTER TABLE customers ADD PRIMARY KEY (customer_id);. Drop a primary key (standard-ish): ALTER TABLE customers DROP PRIMARY KEY; (MySQL) or ALTER TABLE customers DROP CONSTRAINT customers_pkey; (PostgreSQL). Insert valid data: INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'Alice', 'Smith');. Attempting to insert a duplicate: INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'Bob', 'Jones'); results in an error. Inserting NULL: INSERT INTO employees (employee_id, first_name, last_name) VALUES (NULL, 'Carol', 'White'); also fails. These examples illustrate the core behavior across standard SQL implementations.
Common pitfalls
A frequent mistake is assuming a primary key must be a single column; composite keys are valid and sometimes necessary. Another pitfall is using a nullable column as part of a primary key; the standard forbids NULLs, but some systems may allow it if NOT NULL is not enforced. Changing a primary key after data exists can be costly and may require dropping and recreating foreign keys. Using natural keys (e.g., email addresses) as primary keys can lead to problems if values change; surrogate keys are often preferred. Overlooking the automatic index creation can lead to redundant indexes if you also create a separate unique index on the same columns. In some databases, dropping a primary key automatically drops the underlying index, which may affect performance. Also, primary keys are not automatically propagated to foreign keys; you must define foreign key constraints explicitly. Finally, deferring primary key checks can cause unexpected failures at commit time, complicating error handling.
Best practices
Choose a primary key that is stable, minimal, and unlikely to change. Surrogate keys (e.g., auto-incremented integers or UUIDs) are often good choices because they are meaningless and immutable. Avoid using sensitive or frequently updated columns as primary keys. Define the primary key at table creation time to avoid later alterations. Use a consistent naming convention for constraints, such as pk_table_name. For composite keys, ensure the combination is truly unique and necessary; otherwise, consider a surrogate key with a unique constraint on the natural key. Be aware of index implications: the primary key index is often clustered, so a narrow key improves performance. Document the primary key in schema diagrams. When designing foreign keys, reference the primary key of the parent table. Regularly review primary keys as part of schema maintenance. In distributed databases, consider how primary key generation affects scalability, and choose appropriate strategies like sequences or UUIDs.
Frequently asked questions
Q: Can a table have multiple primary keys? A: No. A table can have at most one primary key, but that key can consist of multiple columns (composite key). Q: Is a primary key automatically indexed? A: Yes, most relational database systems automatically create a unique index to enforce the primary key. Q: Can a primary key column contain NULL? A: No. The SQL standard requires primary key columns to be NOT NULL. Some systems may allow it in rare cases, but it is non-standard and not recommended. Q: How does a primary key differ from a unique constraint? A: A primary key implies uniqueness and non-nullability, and there can be only one per table. A unique constraint also enforces uniqueness but allows NULLs (except in some systems) and multiple unique constraints can exist per table.