Tuesday, April 19, 2016

SQL ALTER

By 'ALTER' SQL command  we can add a  new column in a table. It  used to add, modify, delete existing column. You can also add and delete constraints from existing table.
For adding a new column in a table.

ALTER TABLE TableName ADD ColumnName DataType;

For dropping a existing column in a table.

ALTER TABLE TableName DROP COLUMN ColumnName;

For changing data type of a column in a table.

ALTER TABLE TableName MODIFY COLUMN ColumnName datatype;

For add NOT NULL  constraint to a column in a table

ALTER TABLE  TableName MODIFY ColumnName datatype NOT NULL;

For add unique key constraint column in a table.

ALTER TABLE TableName
ADD CONSTRAINT NameUniqueconstraint UNIQUE(Column1, Column2, Column3);

For add primary key constraint column in a table.

ALTER TABLE TableName
ADD CONSTRAINT NameUniqueconstraint  PRIMARY KEY (Column1);

For dropping the constraint.

ALTER TABLE TableName
DROP CONSTRAINT NameUniqueconstraint;

You can think that this a student table.

ID
Name
AGE
ADDRESS
1
A.F.M ERFANUL ISLAM
12
Delhi
2
ENAN
20
Mumbai
3
HAFIZ
21
Indore
4
LOVELY
34
Bd

This a example to add new column in the table:

ALTER TABLE STUDENT ADD SEX char(1);

Now student table changed  and this is the output of select statement


ID
Name
AGE
ADDRESS
SEX
1
A.F.M ERFANUL ISLAM
12
Delhi
NULL
2
ENAN
20
Mumbai
NULL
3
HAFIZ
21
Indore
NULL
4
LOVELY
34
Bd
NULL

No comments:

Post a Comment