How to Create a Primary Key in MySQL

Primary keys are a fundamental part of relational database management systems (RDBMSs). They help us to maintain data integrity.

We can create primary keys when we create the table, or we can add one later.

When we create the primary key with the table, we have the option of defining the key within the actual column definition, or as a separate clause after all column definitions.

Define a Primary Key in the Column Definition

Here’s an example of defining the primary key in the column definition:

CREATE TABLE Person
(
    PersonId INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Here we can see that PRIMARY KEY is included in the definition for the PersonId column. This makes the PersonId column the primary key for the table.

Note that I also defined the primary key column as NOT NULL. Primary key columns cannot be nullable.

However, if I hadn’t included NOT NULL, MySQL would have automatically set it to NOT NULL anyway due to it being defined as the primary key.

In MySQL, all primary keys are automatically assigned the name PRIMARY.

Define a Primary Key in a Separate Clause

We can also define primary keys in a separate clause (after all columns have been defined):

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT NOT NULL,
    VoucherCode INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    PRIMARY KEY (PersonId)
);

So this time, instead of specifying PRIMARY KEY within the PersonId column definition, I added a clause at the end of the CREATE TABLE statement.

When we do this, we include the column name in parentheses. We can also use this syntax to create a composite primary key by including multiple columns, separated by a comma.

Create a Composite Primary Key

Composite primary keys are primary keys that are made up of multiple columns. So instead of just one column being defined as the primary key for the table, two or more columns are defined as being the primary key.

When we do this, the table still only has one primary key. It’s just that the primary key is made up of more than one column. So it’s a single primary key consisting of multiple columns.

Here’s an example of creating a composite primary key for our table:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT NOT NULL,
    VoucherCode INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    PRIMARY KEY (PersonId, VoucherCode)
);

Here I specified that the primary key be made up of two columns: the PersonId column and the VoucherCode column.

Notice that the PersonId column is NOT NULL but the VoucherCode isn’t. Well, MySQL will automatically set VoucherCode to NOT NULL when we include it in a primary key. Primary keys can’t be nullable, and so MySQL does this behind the scenes for us.

A composite primary key would normally be used on a table where no single column would contain unique values, but two columns combined would result in a unique value for each row in the table.

Define an Existing Column as the Primary Key

If a table has already been created without a primary key, we can still create a primary key for that table. We can use the ALTER TABLE statement to modify an existing column or create a new column and define it as the primary key.

Here’s an example of defining an existing column to be the primary key:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT,
    VoucherCode INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

ALTER TABLE Person 
MODIFY COLUMN PersonId INT NOT NULL PRIMARY KEY;

In this case I created the table without a primary key. I then used the ALTER TABLE statement to set the PersonId column as the primary key column for the table. When I did this, I redefined the column, including its data type, NOT NULL status, and of course the PRIMARY KEY. As mentioned, we can omit the NOT NULL part, and MySQL will automatically define the column as NOT NULL if we set it to be the primary key.

Add a Column as the Primary Key

As mentioned, we can also add a whole new column to be the primary key:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

ALTER TABLE Person 
ADD COLUMN PersonId INT NOT NULL PRIMARY KEY;

All we did was use ADD COLUMN instead of MODIFY COLUMN.

Omitting `NOT NULL` in the Column Definition

Primary key columns cannot be nullable. In other words, they must have NOT NULL in their definition.

If we forget to include NOT NULL in the column definition, all is not lost. MySQL will silently add this behind the scenes.

In other words, if we define a column as the primary key, that column will be defined as NOT NULL whether we like it or not.

Of course, we should like it. After all, we need the data in this column to be unique across all rows, and by making it NOT NULL helps us achieve this goal.

Let’s test this out:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

We can see that the code ran successfully without error.

Let’s use the SHOW CREATE TABLE statement to show us the table’s definition:

SHOW CREATE TABLE Person;