How to create second primary column ( Auto Increment)

Question

i m having the table like this

------------------------
Id  |  MergeId  | name |
------------------------
1   |   M1      | Riya |
2   |   M2      | diya |
3   |   M3      | tiya |
------------------------

MergeId is already assigned as Primary key, now i want a new column ID (AutoIncrement), but when i try to create its shows me like "cant create already a table should have only one primary key" but i cant change my MergeId from Primary to other constrains. please someone help, thankyou

Query

ALTER TABLE  `merge_info` ADD  `id` INT( 11 ) NOT NULL AUTO_INCREMENT FIRST ,
ADD PRIMARY KEY (  `id` ) ,
ADD INDEX (  `id` ) ;

Error

#1068 - Multiple primary key defined

I'm confused by your question; you can of course make a column auto-incrementing without it being a primary key. Is that really the only issue here, or is there something else? — Brian Driscoll, Aug 04 '14 at 19:08
Please show the code that you are using to add the auto-increment column. — Gordon Linoff, Aug 04 '14 at 19:09

score 4 · Accepted Answer · answered Aug 04 '14 at 19:15

To be an auto-increment column - a columns need to either be the primary key or having an index. Add an index to the id column. Then you can make it auto-increment

alter table merge_info add index id (id);
ALTER TABLE merge_info modify COLUMN id int auto_increment;

SQLFiddle demo

score 1 · Answer 2 · answered Aug 04 '14 at 19:09

As the error message indicates you can only have one primary key per table. You may be able to have a composite primary key (that is a primary key composed of multiple columns). You can add constraints to make a column behave similarly to a primary key. You can require NOT NULL and UNIQUE, add AUTO_INCREMENT semantics, and add an index on the column (or composite group of columns).

(Clarification: you can apply apply AUTO_INCREMENT to individual columns ... the constraints and indexing can be done on composites as well as single columns).

score 1 · Answer 3 · edited May 23 '17 at 12:06

1

You can only have one primary key. You can however set the column to auto increment. I think what your trying to get at is joining that table with another, is that correct? I believe they are called foreign keys but I think thats only with MSSQL, never used them with MySQL.

Auto increment is a matter of what your doing. PHPMyAdmin you just select it as one of the column attributes.

PHPMyAdmin:

Auto increment in phpmyadmin

SQL:

CREATE TABLE table
(
ID int NOT NULL AUTO_INCREMENT,
ID2 int NOT NULL AUTO_INCREMENT.
PRIMARY KEY (ID)
)

As per Jim:

SQL:

CREATE TABLE table
(
ID int NOT NULL AUTO_INCREMENT,
ID2 int NOT NULL,
PRIMARY KEY (ID),
UNIQUE (ID2)
)

After thought:

Check out this link, it may be of more benefit than any of this other stuff. Please be more specific next time.

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

edited May 23 '17 at 12:06

Community

1
1

answered Aug 04 '14 at 19:12

Joe Kasavage

503
5
13

1

Might want to suggest adding UNIQUE constraint as well. This would more closely match the semantics of a PRIMARY KEY. – Jim Dennis Aug 04 '14 at 19:14
No No No! You can only have one auto_increment column on a table and it must be a key. – Arth Aug 04 '14 at 19:17
Whoops, my mistake. Thanks for catching that! – Joe Kasavage Aug 04 '14 at 19:19

How to create second primary column ( Auto Increment)

3 Answers3