SQL Alter Statement

Alter Statement:

Alter statement is used to modify table and its contents. Alter statement is used for making changes in tables. You can change column data type, add column, delete column and also you can add and remove constraints by using alter statement. Let’s see in how many different ways alter statement can be used.

General Syntax for alter statement:

alter table table_name add,drop column,constraint

Suppose we have a table Doctor with following columns and their data types.

Column Name	Data Type
doctor_id	varchar(20)
name	varchar(20)
Fee	int
timings	char(10)

Assume we have some data in it as follow.

doctor_id	name	Fee	timings
D101	John	10000	8-4
D102	Peter	20000	4-8
D103	Nash	30000	12-8
D104	Andrew	25000	6-12
D105	Smith	15000	6-12

Adding a column

Suppose that we want to add a column in the table we missed earlier that is Specialty with data type varchar (20) we will write like this.

alter table doctor add specialty varchar(20)

Now when we will execute this command a column with name specialty will be added in table.Now write a select statement which selects all the columns and rows as

select * from doctor

You will see the table as follow

doctor_id	name	Fee	timings	specialty
D101	John	10000	8-4	NULL
D102	Peter	20000	4-8	NULL
D103	Nash	30000	12-8	NULL
D104	Andrew	25000	6-12	NULL
D105	Smith	15000	6-12	NULL

As we have added this column after we have inserted some data so this column will initially contain NULL.Now you can update table and add data into this column.

Deleting a column

If we want to delete a column which is no longer useful or we have accidently added it.

alter table doctor drop column specialty

This will delete the column specialty from table.

Changing column data types:

1

alter table doctor

alter column fee money

This will change data type of column fee from int to money

Column Name	Data Type
doctor_id	varchar(20)
name	varchar(20)
Fee	Money
timings	char(10)

2

alter table doctor

alter column name varchar(50)

This will change length of name column from 20 to 50

Column Name	Data Type
doctor_id	varchar(20)
name	varchar(50)
Fee	Money
timings	char(10)

Adding Constraints:

Constraint means some restriction some rule.When we add a constraint on a column it must follow that constraint other wise data will not be saved for that column.Let’s see an example.

1

 alter table doctor add constraint def_value default('0') for fee

Here we added a constraint on column fee with name def_value.

This constraint checks that if value for fee column in not specified it will assign the default value for it which is 0 given in constraint

Now if we write an insert statement like this

insert into doctor(doctor_id,name,timings) values('d106','kaly','12-8')

Here we have not specified the value for fee column,the constraint we added for fee column will automatically assign 0 to fee column.As you can see in the table.

doctor_id	name	Fee	timings
D101	John	10000	8-4
D102	Peter	20000	4-8
D103	Nash	30000	12-8
D104	Andrew	25000	6-12
D105	Smith	15000	6-12
d106	kaly	0	12-8

2

alter table doctor add constraint unique_name unique(name)

This constraint will check that no name is repeated in the table if you insert a name which is already in the table this constraint will not allow you to insert that name and an error will be displayed.

Let’s see what happens if we want to insert a value for name column which is already in the table

John already exists in the table.

 insert into doctor values('D106','john',10000,'12-8')

Following error will be displayed

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'uniq_name'. Cannot insert duplicate key in object 'dbo.doctor'.

The statement has been terminated.

3

alter table doctor add constraint doc_pk primary key(doctor_id)

This constraint sets the doctor_id column as Primary key which means duplicate and null values can’t be inserted for this column.

4

You can aslo add aconstraint for foreign key.Suppose we have two tables like this.

alter table doctor add constraint Fk_app foreign key(doctor_id)

references appointment(doctor_id)

Here we are creating a foreign key for column doctor_id which refers appointment table.

Deleting Constraints:

As we have added constraitnts we can also delete these constarints.

Syntax for deleting:

alter table table_name drop constraint constraint_name

Let’s suupose we want to delete constraint we have added earlier.

alter table doctor drop constraint def_value

This will delete constraint def_value.This constraint was added to assign default value for fee column if user has not specified value for fee column.Similarly you can drop any constraint you want.