ALTER
The ALTER keyword is
used to add, delete or modify columns in an existing table and you can add and
drop various constraints on an existing table.
Syntax: The basic syntax of ALTER TABLE is below in
different scenario’s.
Add a new column in existing table
ALTER TABLE your_table_name
ADD column_name DataType;
DROP COLUMN in existing table
ALTER TABLE your_table_name
DROP COLUMN column_name;
Modify DATA TYPE of a column in table
ALTER TABLE
you_table_name MODIFY COLUMN column_name DataType;
Add a NOT NULL constraint to a column in a table
ALTER TABLE your_table_name
MODIFY column_name datatype NOT NULL;
ADD UNIQUE CONSTRAINT to a table
ALTER TABLE your_table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ADD CHECK CONSTRAINT to a table
ALTER TABLE your_table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
ADD PRIMARY KEY constraint to a table
ALTER TABLE your_table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
DROP CONSTRAINT from a table
ALTER TABLE your_table_name
DROP CONSTRAINT MyUniqueConstraint;
DROP PRIMARY KEY constraint from a table
ALTER TABLE your_table_name
DROP CONSTRAINT MyPrimaryKey;
Example:
EMPLOYEE table have
the following records:
|
Id
|
Name
|
Age
|
Address
|
|
1
|
Alok Kumar Singh
|
30
|
IN
|
|
2
|
Tomas Paul
|
45
|
NZ
|
|
3
|
Cristomfer Dee
|
85
|
AU
|
|
4
|
Niel Macengi
|
55
|
US
|
Below is the example to
ADD a new column in an existing table
USE SQLCLR
GO
ALTER TABLE EMPLOYEE ADD Department varchar(500);
GO
|
Id
|
Name
|
Age
|
Address
|
Department
|
|
1
|
Alok Kumar Singh
|
30
|
IN
|
NULL
|
|
2
|
Tomas Paul
|
45
|
NZ
|
NULL
|
|
3
|
Cristomfer Dee
|
85
|
AU
|
NULL
|
|
4
|
Niel Macengi
|
55
|
US
|
NULL
|
Below is the example
to DROP Department column from existing
table:
USE SQLCLR
GO
ALTER TABLE EMPLOYEE DROP COLUMN Department;
GO
|
Id
|
Name
|
Age
|
Address
|
|
1
|
Alok Kumar Singh
|
30
|
IN
|
|
2
|
Tomas Paul
|
45
|
NZ
|
|
3
|
Cristomfer Dee
|
85
|
AU
|
|
4
|
Niel Macengi
|
55
|
US
|
