Sunday, March 31, 2019

ALTER | DDL Command | Alter Command In SQL

ALTER Command

Alter command used to add, modify and delete columns and constraints in an existing table.

ALTER TABLE | ALTER Command with table

Syntax :

ALTER TABLE table_name ADD column_name datatype /
                                              MODIFY COLUMN column_name datatype /
                                              DROP COLUMN column_name ;

In SQL ALTER command, there is two type of parameter :

1) table_name - This parameter specifies the name of table
2) column_name - This parameter specifies the name of column.

We can do three things like, add, modify and delete table column and constraints using with alter command of SQL Server. Which are as under :

1) ALTER TABLE -  ADD column SQL Server


Syntax :

ALTER TABLE table_name ADD column_name datatye;

Example :

// Add Purchase_Item column in Customer01 table

ALTER TABLE Customer01 ADD Purchase_Item Varchar(255);

2) ALTER TABLE - DROP column SQL Server | SQL DROP Column


Syntax :

ALTER TABLE table_name DROP column_name;

Example :

// DROP City column from Customer01 table

ALTER TABLE Customer01 DROP City;

Above example for SQL Server DROP column

Above example for alter table drop column.

3) ALTER TABLE - MODIFY  column SQL Server

Syntax :

ALTER TABLE table_name MODIFY column_name datatype;

Example :

// Change the data type of Phone_No column

ALTER TABLE Customer01 MODIFY Phone_No varchar(255);

DROP CONSTRAINT  | ALTER Command with Constraint


Syntax :

ALTER TABLE table_name ADD constraint_name (column_name) /
                                              MODIFY COLUMN constraint_name (column_name) /
                                              DROP COLUMN constraint_name (column_name) ;

ADD Constraint

Syntax :

ALTER TABLE table_name ADD constraint_name (column_name);

Example :

// ADD PRIMARY KEY constraint to Cust_Id column

ALTER TABLE Customer01 ADD PRIMARY KEY (Cust_Id);

DROP Constraint

Syntax :

ALTER TABLE table_name DROP constraint_name (column_name);

Example :

// DROP UNIQUE constraint from First_Name column

ALTER TABLE Customer01 DROP UNIQUE (First_Name);

MODIFY Constraint

Syntax :

ALTER TABLE table_name MODIFY constraint_name(column_name);

Example :

// Change CHECK constraint value

ALTER TABLE Customer01 MODIFY CHECK (C_Item >= 5);

* * * * * * * * * * * * * * * * *

In Easy Language (Simplify)


Alter command used to add, modify and drop column and constraint of an existing table.



Previous Post
Next Post

post written by:

0 Comments: