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