Advertisement

Latest Post

Friday, 16 September 2016

ALTER Sql keyword

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





Government Jobs