ALTER command in SQL

ALTER command in SQL is used to change or modify the structure of existing objects available in the database.

Using ALTER command we can:

1. Increase /decrease width of column
2. Change the data type of column
3. Change NOT NULL to NULL and vice-versa
4. Add a new column to a table
5. Drop a column from a table
6. Delete a column from a table
7. Add a new table constraint
8. Drop a table constraint
9. Set a default for a column
10. Drop a default for a column

When a table doesn’t contain any data in it, we can perform any type of action on it. But, if it contains data in it, we can perform the actions with restriction.

Syntax:

ALTER Table <table_name> ALTER column <column_name> <data type> [<width>] [NOT NULL |NULL]

Examples:

ALTER table Students Cname varchar (200)

ALTER table Students ADD City varchar (100)

ALTER table Students ADD Fees_ck Check (Fees Between 5000 to 7000)

ALTER table Students DROP CONSTRAINT Balance

ALTER table Student MODIFY Balance SET DEFAULT 2000

ALTER table Students Balance NOT NULL

Changing NULL to NOT NULL is same as imposing NOT NULL constraint on column. While changing NULL to NOT NULL, the NULL column should not contain any NULL values in it.