otak info official Logo   
OTAK.INFO
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 : 3330


Harry
Harry
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.

To add CONSTRAINT UNIQUE column
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.



www.OTAK.INFO
Since 19 January 2007
Page hit : 664,221

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