Create Table in SQL with a Primary Key: Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

A primary key is the column or set of columns that uniquely identifies each row in a table. When you create table SQL primary key constraints, the database enforces two rules for you: no two rows can share the same key value, and no key column can hold a null. That guarantee is what makes reliable joins and lookups possible.

Quick Answer

A primary key is declared inside CREATE TABLE with PRIMARY KEY after the column definition, or as a separate table-level clause.
The database automatically builds a unique index on the key, so lookups by key are fast.
A table can have only one primary key, but that key can span several columns (a composite key).
Primary key columns are implicitly NOT NULL, so you do not need to add that constraint yourself.
Every foreign key in another table should point at a primary key or a unique constraint, which is what keeps joins meaningful.

Before You Start

You need a database connection and permission to create objects in the target schema. In PostgreSQL, CREATE TABLE creates a new, initially empty table owned by the user who runs the command, and the table name must be distinct from any other relation in the same schema, including views and sequences [1].

Decide three things before you write the statement.

First, what makes a row unique. A customer table might use a generated customer number. An order line table usually needs the order number plus the line number together.

Second, the data type. Integer keys are compact and index well. Text keys work when you already have a natural identifier such as a country code, but they take more space in every index.

Third, whether you want the database to generate values. Many systems offer an identity or serial style column so you never have to invent the next number yourself.

One naming habit pays off later: give the constraint an explicit name such as pk_customers. Named constraints are far easier to drop or alter than auto-generated ones.

Step by Step

Write the basic CREATE TABLE statement. List your columns with their data types. Leave the key decision for the next step so you can see the whole shape of the table first.

Choose the key column or columns. If one column is already unique and never null, use it. If uniqueness only holds across a combination, plan a composite key.

Add the inline PRIMARY KEY clause. Put it directly after the column definition when the key is a single column. This is the shortest form and the one most readers expect.

Or add a table-level constraint. Use a separate CONSTRAINT name PRIMARY KEY (col1, col2) line when the key spans multiple columns or when you want a readable constraint name.

Let the database generate values if you need to. Add an identity or serial property to the key column so inserts do not have to supply a value.

Create the table and check the result. Run the statement, then inspect the table definition in your client to confirm the key exists.

Test the constraint. Insert one row, then try to insert a duplicate key value. The second insert should fail. That failure is the feature working.

Here is the single-column form:

CREATE TABLE customers (
    customer_id   integer GENERATED ALWAYS AS IDENTITY,
    email         text NOT NULL,
    created_at    date NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

And here is the composite form, where neither column is unique alone:

CREATE TABLE order_lines (
    order_id      integer NOT NULL,
    line_number   integer NOT NULL,
    product_code  text NOT NULL,
    quantity      integer NOT NULL,
    CONSTRAINT pk_order_lines PRIMARY KEY (order_id, line_number)
);

The inline version is equivalent for a single column:

CREATE TABLE countries (
    country_code  char(2) PRIMARY KEY,
    country_name  text NOT NULL
);

Worked Example

Take a small sales dataset with two tables: customers and orders. Each customer has a generated customer_id. Each order belongs to exactly one customer.

The customers table uses customer_id as its primary key. The orders table uses order_id as its primary key and adds a customer_id column as a foreign key pointing back at customers.

CREATE TABLE orders (
    order_id      integer GENERATED ALWAYS AS IDENTITY,
    customer_id   integer NOT NULL,
    order_date    date NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

Now suppose you insert two customers and three orders. The customers table holds 2 rows, the orders table holds 3 rows. Because customer_id is unique in customers, a join between the two tables returns exactly 3 rows, one per order, with no duplicated customer rows. If customer_id were not unique, that same join could return more rows than there are orders, and any total built on top of it would be inflated.

That is the practical value of the constraint. It is not decoration. It is the condition that makes a one-to-many join behave the way you expect.

The same logic scales. A join between a 1,000-row fact table and a 50-row dimension table returns 1,000 rows when the dimension key is a primary key. Without uniqueness, the row count depends on how many duplicates happen to exist, and your aggregate totals change with them.

Other Ways to Do It

You are not limited to declaring the key at creation time.

Add a key to an existing table. Use ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (...) on a table that has no key yet. The columns must already be unique and not null, or the statement fails.

Use a unique constraint instead. A UNIQUE constraint also prevents duplicates, but it allows nulls and a table can have several of them. Use PRIMARY KEY for the row identifier and UNIQUE for other candidate keys such as an email address.

Use a surrogate key with a natural unique constraint. Many teams add a generated integer key for joins and keep a UNIQUE constraint on the business identifier. This keeps foreign keys narrow while still protecting the real-world rule.

Partition large tables. In PostgreSQL, a partitioned table is divided into sub-tables created with separate CREATE TABLE commands, and the parent table itself is empty. Rows are routed to a partition based on the partition key, and an error is reported if no partition matches [1]. The primary key on a partitioned table must include the partition key columns.

Reuse the pattern in views and queries. Once keys exist, you can wrap joins in a common table expression or save a query as a view without repeating the key logic each time.

Troubleshooting

"Multiple primary keys for table" error. You declared PRIMARY KEY twice, often once inline and once at table level. Keep one declaration and remove the other.

"Column does not exist" in the constraint. The column name in the table-level clause does not match any column definition. Check spelling and case.

Duplicate key value violates unique constraint. The table already contains the value you are inserting. Either pick a new value or fix the source data.

Null value in a primary key column. You are inserting an explicit null into a key column. Let the database generate the value, or supply a real one.

Foreign key violation on insert. The parent row does not exist yet. Insert the parent first, or check that you are referencing the right column.

Slow inserts after adding a key. Every insert now updates a unique index. That cost is normal and usually worth it. If bulk loading is slow, load first and add the key afterward.

Common Mistakes

Choosing a column that can change. Email addresses and phone numbers get reassigned. Use a stable identifier and put a UNIQUE constraint on the changeable one instead.
Forgetting the key on a child table. A join table with no primary key allows the same pair of rows to be inserted twice. Add a composite key over both foreign key columns.
Assuming UNIQUE and PRIMARY KEY are interchangeable. UNIQUE permits nulls and you can have many of them. PRIMARY KEY permits one per table and forbids nulls.
Leaving the constraint unnamed. Auto-generated names differ between systems and are hard to reference later. Name every key explicitly.
Using a composite key when a surrogate would be simpler. Wide keys get copied into every foreign key and every index. If the natural key is long, consider a generated integer key plus a unique constraint.
Adding a key to a table that already has duplicate rows. The ALTER TABLE will fail. Deduplicate first, then add the constraint.

Limitations

A primary key guarantees uniqueness inside one table. It does not validate that a value is correct, that a foreign key points at a row that still exists after a delete, or that your data is free of other kinds of duplication. Two rows can hold the same customer name and still both be valid rows, because the key is the identifier, not the content.

The constraint also cannot fix a bad key choice. If you pick a column that is unique today but not guaranteed to stay unique, the database will enforce the rule until it breaks in production. And on very large tables, adding a primary key later can take significant time and disk space because the index must be built across every row. Plan the key before the table fills up.

Frequently Asked Questions

Can a table have more than one primary key?

No. A table can have exactly one primary key. That key may consist of several columns, which is called a composite or compound key. If you need to enforce uniqueness on other columns, add UNIQUE constraints for each of them.

Is a primary key automatically indexed?

Yes. The database creates a unique index to enforce the constraint, and that index is what makes lookups by key fast. You do not need to create a separate index on the key columns.

Can a primary key column contain null values?

No. Primary key columns are implicitly NOT NULL in PostgreSQL, MySQL and SQL Server. If you try to insert a null into a key column, the statement fails. SQLite is an exception: for historical reasons it allows nulls in a primary key column that is not an INTEGER PRIMARY KEY unless you also declare it NOT NULL or use a STRICT or WITHOUT ROWID table. This is one of the main differences between a primary key and a UNIQUE constraint, which does allow nulls.

What is the difference between a primary key and a foreign key?

A primary key identifies rows in its own table. A foreign key is a column in one table that references the primary key or a unique key in another table. The foreign key is what lets you join the two tables and what prevents orphan rows.

Should I use a natural key or a generated key?

Use a generated key when the natural identifier is long, can change, or is not yet known at insert time. Use a natural key when it is short, stable, and genuinely unique, such as an ISO country code. Either way, put a UNIQUE constraint on the natural identifier so the business rule is still enforced.

Does the primary key affect join performance?

Yes. Joining on an indexed key column is much faster than joining on an unindexed column, because the database can look up matching rows directly. When you write an inner join or a full outer join, the key columns are usually the join condition. If you later group results by a non-key column, a partition by clause can help you compute per-group values without collapsing the rows.

References

PostgreSQL: Documentation: 18: CREATE TABLE