SQL Alter Command



SQL Alter Command :

The SQL ALTER TABLE command is used to –

  1. Add a column in an existing table
  2. Delete a column in an existing table
  3. Modify columns in an existing table
  4. Drop constraints on the existing table

Syntax : SQL Alter Command

OperationCommand / SQL Query
Add a column in Table
ALTER TABLE table_name 
ADD column_name datatype;
Drop a Column
ALTER TABLE table_name 
DROP COLUMN column_name;
Change Data Type
ALTER TABLE table_name 
MODIFY COLUMN column_name datatype;
Adding Not Null Constraints
ALTER TABLE table_name 
MODIFY column_name datatype NOT NULL;
Adding Unique Constraints
ALTER TABLE table_name 
ADD CONSTRAINT uniqueConstraint 
UNIQUE(column1, column2...);
Adding Check Constraints
ALTER TABLE table_name 
ADD CONSTRAINT uniqueConstraint 
CHECK (Condition);
Adding Primary Key
ALTER TABLE table_name 
ADD CONSTRAINT primaryKey 
PRIMARY KEY (column1, column2...);
Drop Constraints
ALTER TABLE table_name 
DROP CONSTRAINT uniqueConstraint;
Drop Primary Key
ALTER TABLE table_name 
DROP CONSTRAINT primaryKey;

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Omkar    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following query should be executed to Add a new column in an existing table :

ALTER TABLE CUSTOMERS ADD GENDER char(1);

Now, CUSTOMERS table is changed and following would be output from SELECT statement:

+----+----------+-----+-----------+----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |  GENDER  |    
+----+----------+-----+-----------+----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |          |
|  2 | Raj      |  23 | Delhi     |  1500.00 |          |
|  3 | Anand    |  21 | Karachi   |  2000.00 |          |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |          |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |          |
|  6 | Komal    |  23 | Pune      |  4500.00 |          |
|  7 | Omkar    |  24 | Indore    | 10000.00 |          |
+----+----------+-----+-----------+----------+----------+

Following query is used to DROP GENDER column from existing table:

ALTER TABLE CUSTOMERS DROP SEX;

CUSTOMERS table would be like this after select statement :

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Omkar    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+