MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Primary Key Indexes / Question No: 148

How to create multi column primary key index in MySQL?

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?

Answer No: 148

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.

Related MySQL FAQs to the Above FAQ

How-to-create-primary-key-index-in-MySQL How to create primary key index in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.