SQL Alter Command
SQL Alter Command :
The SQL ALTER TABLE command is used to -
- Add a column in an existing table
- Delete a column in an existing table
- Modify columns in an existing table
- Drop constraints on the existing table
Syntax : SQL Alter Command
| Operation | Command / 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 | +----+----------+-----+-----------+----------+