Home Sql Mysql-alter Mysql Alter Column Primary Key

Mysql Alter Column Primary Key
Posted on: January 13, 2009 at 12:00 AM

Mysql Alter Column Primary Key is used to modify table and redefine the Primary Key Column in a table.

Mysql Alter Column Primary Key

Mysql Alter Column Primary Key is used to modify table and redefine the Primary Key Column in a table.

Understand with Example

The Tutorial illustrate an example from 'Mysql Alter Column Primary Key'. To understand an example, we create a table employees whose primary key is Empid.

Query to create table:

CREATE TABLE `employees` (
            ->Empid int(10),  
            ->Empname varchar(60),
             ->date date 
	     ->PRIMARY KEY(Empid)
           ->);

Query to insert data into Table named employees:

The Query insert into add the records or rows to the table 'employees'.

mysql>insert into employees values(01,'Girish','2008-12-22');
Query OK, 1 row affected (0.02 sec)

mysql>insert into employee1 values(02,'Komal','2008-12-23');
Query OK, 1 row affected (0.02 sec)

Query to view data of Table named employees:

To view the details from table 'employees' we use select query that return the detail from 'employees'.

mysql> select * from employee1;

Output:-

+-------+---------+------------+
| Empid | Empname | date       |
+-------+---------+------------+
|     1 | Girish  | 2008-12-22 |
|     2 | Komal   | 2008-12-23 |
+-------+---------+------------+
2 rows in set (0.00 sec)

Query to view datatype of Table named employees:

mysql> describe employees;

Output:-

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Empid   | int(10)     | NO   | PRI | 0       |       |
| Empname | varchar(60) | YES  |     | NULL    |       |
| date    | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Query for altering the Primary key of the table employees:

The Query below is used to modify the table 'Employees' and remove the primary key from column name 'Empid'.

Step1:-Drop the primary key

mysql> alter table Employees
    -> drop primary key;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query to view changes occurs in the datatype of Table named employees:

mysql> describe employees;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Empid   | int(10)     | NO   |     | 0       |       |
| Empname | varchar(60) | YES  |     | NULL    |       |
| date    | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Step1:-Define the primary key

Query for defining the Primary key of the table employees:

The Query below is used to modify the table 'employees' and add the EmpName as primary key.

mysql> alter table employees
    -> add primary key (Empname);
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query to view datatype of Table named employees:

Now to view the table field,type,Null,Key etc we use describe keywords that shows you all these details.

mysql> describe employees;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Empid   | int(10)     | NO   |     | 0       |       |
| Empname | varchar(60) | NO   | PRI |         |       |
| date    | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Related Tags for Mysql Alter Column Primary Key:
sql, mysql, c, table, sed, column, key, tab, define, if, to, modify, e, primary, alter, use, im, in, mod, m, alt, modi, redefine, umn, s, col, my, k, is, and, ar, s, s, ri, th, ab, abl, fin, lum, pr, nd, ol, o

«Previous Index Next»