Home / Indexes / Primary Key Indexes / Question No: 148

I want to have a multi column primary key in my table. I need to know about the syntax to create multi column primary key. Can you give an example that show me, how to create multi column primary key index in MySQL?

To create multi columns primary key index is simple as unique index and non-unique or normal index. Since MySQL allows to create primary key index on single column as well as on multi columns (combination of columns) of the table, you can create multi columns primary key index using CREATE TABLE and ALTER TABLE statements provided by the MySQL.

The primary keys are almost always added when creating the table. The following is an example to create multi column primary key index using CREATE TABLE statement:

CREATE TABLE buyers (
   buyer_id INT NOT NULL,
   first_name CHAR(19) NOT NULL,
   last_name CHAR(19) NOT NULL,
   age SMALLINT NOT NULL,
   postal_code SMALLINT NOT NULL,
   PRIMARY KEY (buyer_id, first_name)
);

You can create multi column primary key index after creating your table or when you decide to add it later. In this case, you will have to alter your table. The following is an example to create multi column primary index using ALTER TABLE statement:

ALTER TABLE syntax to add multi columns primary key index.

ALTER TABLE tablename ADD PRIMARY KEY (columns_to_index);

Example to add multi column primary key index using ALTER TABLE statement:

ALTER TABLE buyers ADD PRIMARY KEY (buyer_id, first_name);

Note: only one primary key can be added in a table.