otak info official Logo   
Personal Blog

Hariyanto Lim

정길상 / 鄭吉祥
Home   Last Posts  
Please login to see more topics and discussion categories.
goto main category list >> Database >> MySQL

Title : Change primary key and index field
Total Reply : 0
Total View : 4487

Total thread: 131
Total reply: 50

Post #149
Change primary key and index field
To remove current primary key
alter table nomor_perdana drop primary key;
If the current primary key to be removed is an AUTO_INCREMENT then MySQL error (eg: incorrect table definition; there can be only one auto column and it must be defined as a key) will occurred, so need to modify the type from "int AUTO INCREMENT" to normal "int"
alter table nomor_perdana modify column nomorPerdanaId int;
To drop an index column
alter table nomor_perdana drop index nomorPerdanaId;
NOTE: can not drop index of multiple columns, must drop it 1 by 1.

ALTER TABLE nomor_perdana ADD CONSTRAINT UNIQUE (nomorPerdanaId);
To add a new index (unique constraint) multiple fields
alter table nomor_perdana add index (nomorPerdanaId, memberId, nomorPerdanaNomor);
NOTE: in order for "insert into table ... on duplicate key update ..." to work properly then must follow rule : one of the index MUST BE primary key !!!

To change normal "int" field type to "int AUTO INCREMENT"
alter table nomor_perdana modify column nomorPerdanaId int auto_increment;

Change primary key and index field
Falling in love with the world

Write : 2014-10-29 17:09:38
Last edit : 2014-10-30 18:36:40

If you want to create a new reply then please login first.

Since 19 January 2007
Page hit : 842,267

Code update 24th June 2013
Brain is a very capable to solve big problems
but requires constant reminders about how to.
peace bird