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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
