We have a table basic profile with following table structure
1 2 3 4 5 6 7 8 | create table basic_profile(b_id int(10) NOT NULL AUTO_INCREMENT, id1 int(10), id2 int(10), fname varchar(30), lname varchar(30), mailid varchar(50), address varchar(100), primary key(b_id)); |
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> desc basic_profile; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | b_id | int(10) | NO | PRI | NULL | auto_increment | | id1 | int(10) | YES | | NULL | | | id2 | int(10) | YES | | NULL | | | fname | varchar(30) | YES | | NULL | | | lname | varchar(30) | YES | | NULL | | | mailid | varchar(50) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 7 rows in set (0.06 sec) |
we don’t want to use it as primary key so first we removed its auto increment the we will remove it from primary key
queries for these are
1 2 3 4 5 6 | //remove auto increment ALTER TABLE basic_profile MODIFY b_id INT NOT NULL; //remove from primary key //ALTER TABLE TABLE_NAME DROP PRIMARY KEY ALTER TABLE basic_profile DROP PRIMARY KEY; |
If you want to make primary key to some other column than we can use following query
1 | ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY ; |
above query will not make id1 as auto increment.
to make it a uto incremente we will execute following query insted of above
1 | ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT; |
Now let us check the table description
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> desc basic_profile; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | b_id | int(11) | NO | | NULL | | | id1 | int(11) | NO | PRI | NULL | auto_increment | | id2 | int(10) | YES | | NULL | | | fname | varchar(30) | YES | | NULL | | | lname | varchar(30) | YES | | NULL | | | mailid | varchar(50) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 7 rows in set (0.06 sec) |
We can also make composite primary key in existing table for that we use following query
1 | ALTER TABLE basic_profile ADD PRIMARY KEY(id1,id2) |
This will make id1 and id2 primary key of table.