1

how to set particular column as primary key with auto increment in mysql.am using alter statement . but am getting error when i excecute the alter statement in mysql.

alter statement
---------------

 ALTER TABLE tbl_travelplan add COLUMN TRREQNO id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);
vps
  • 1,337
  • 7
  • 23
  • 41
  • what is the error message? – Sashi Kant Dec 18 '12 at 10:39
  • @sashikant Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id)' at line 1 – vps Dec 18 '12 at 10:41
  • Ask for help but don't provide the error, errors are usually not needed. – N.B. Dec 18 '12 at 10:41

5 Answers5

3

You need to DROP THE PRIMARY KEY FIRST: Try this ::

ALTER TABLE tbl_travelplan add TRREQNO_id INT(11) NOT NULL AUTO_INCREMENT, DROP PRIMARY KEY,PRIMARY KEY;    

ALTER TABLE tbl_travelplan add TRREQNO_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
3

I think the real problem is the space between TRREQNO and id in your query. Try the query i have placed below:

ALTER TABLE tbl_travelplan add TRREQNO_id INT(11) NOT NULL primary KEY AUTO_INCREMENT;
fancyPants
  • 50,732
  • 33
  • 89
  • 96
Bhavik Shah
  • 2,300
  • 1
  • 17
  • 32
0

You have to drop the existing index first using 

DROP_INDEX [your_existing_index] ON tbl_travelplan
Darin Kolev
  • 3,401
  • 13
  • 31
  • 46
0

You can use this query

alter table tbl_travelplan 
    add TRREQNO_id int(11) NOT NULL AUTO_INCREMET PRIMARY KEY
John
  • 723
  • 1
  • 10
  • 12
-1

I think in that table already having primary key. So only these kind of error came. Please remove the primary key and run your query.

Please run this query

ALTER TABLE `table_name` add TRREQNO id INT(11) NOT NULL AUTO_INCREMENT DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `id` )
fancyPants
  • 50,732
  • 33
  • 89
  • 96
somasundaram
  • 139
  • 4