Home » SQL ALTER TABLE

SQL ALTER TABLE

SQL ALTER TABLE statement is used to modify the data type of a column,  add a new column, delete an old column from table, add a new constraints and remove an existing constraints on table.

 

Lets look at an example of Alter table statement in SQL Server.

First we Create a sample table named as Student.

CREATE TABLE Student 
(StudId INT NOT NULL)

Now we have a table Student table, and it has only one column called StudId, as you can see below.

Lets see all the options that you can perform with alter command.

ALTER TABLE ADD COLUMN

It is used to add a new column in an existing table.

SYNTAX

Alter tabletable_nameadd column_name datatype;

Following SQL Statement adds , a Stud_Code column in Student table.

ALTER TABLE Student ADD Stud_Code VARCHAR(10);

 

As you see, a new column Stud_Code is added in Student table now.

SELECT * FROM Student

ALTER TABLE ALTER COLUMN

It is used to modify the data type of an existing column in a table.

SYNTAX

alter tabletable_namealter columncolumn_name datatype ;

Following SQL statement modifies the data type size of column Stud_Code from VARCHAR(10) to VARCHAR(20) .

Before modifying the datatype size of Stud_Code column, Lets verify the current datatype size of Stud_code column.

Using SQL Server System Stored Procedure Sp_Help you can get all the schema information of any table.


EXEC SP_HELPstudent

 

As you can see, column Stud_Code is of data type varchar(10), Lets changes the datatype size from varchar(10) to varchar (20) .

Following SQL Statement changes the datatype size of an existing column Stud_Code in table Studentfrom varchar(10) to varchar (20) .

ALTER TABLE Student ALTER COLUMNStud_Code VARCHAR(20)

Lets check, whether the size of column is changed to VARCHAR(20) or not yet.

EXEC SP_HELP Student


You can see, now datatype size has been changed to VARCHAR(20)

ALTER TABLE DROP COLUMN

It is used to delete an existing column from table.

SYNTAX   

alter tabletable_namedrop column column_name;

Following SQL statement deletes the column Stud_Code from Student table.


ALTER TABLE Student DROP COLUMN Stud_Code

 

Now you can check student table, and you will see now there is no Stud_Code column in table.  That means it has been deleted from table.

SELECT * FROM Student

ALTER TABLE ADD CONSTRAINTS

It is used to create a constraints on an existing table column.

Constraints are used to specify rules for the data in a table and ensures the accuracy and reliability of the data in the table. You can also read more about Constraints CHECK Constraint, Unique Key Constraint, and Default Constraint.

SYNTAX

alter tabletable_nameadd constraint constraint_name condition  ;

Following SQL statement adds a constraints on StudId column to ensure that value for StudId column should always be greater than 0 .

In statement C_Student_StudId is a name for constriant, you can give any name for constraint.  Constraint name is used if you want to delete constraint from column using T-SQL that time you need to provide constraint name.

ALTER TABLE Student 
ADD CONSTRAINT C_Student_StudId CHECK( StudID >0 )

 

 

As you can see command completed successfully, To ensure that a CHECK constraint is added on a table column StudId or not , Lets execute the  Sp_Help command.

EXEC SP_HELP Student

 

As you see , a constraint is added on table successfully and the constraint name is C_Student_StudId.

Now try to Insert a zero (0) value for StudId column in table to see whether a  constraint allows zero value or not.

INSERT INTO Student (StudID)
VALUES (0)

As you can see, it returns an error means constraint is validating for check (studid >0 ), so will not allow value that is less then or equal to 0.

ALTER TABLE DROP CONSTRAINTS

It is used to drop an existing constraint on column. For deleting a constraint from table column using T-SQL ,you need a constriant name.

If you do not know constraint name, then you can use SP_HELP command. It returns all the information about table.

We have already seen above that the name of constraint created on StudId column is C_Student_StudId.

SYNTAX

alter tabletable_name drop constraintconstraint_name;

Following statement deletes an existing constraint, that you recently created CHECK Constraint (StudId>0) on cloumn StudId.  

ALTER TABLE Student DROP CONSTRAINT C_Student_StudId

 

 

As you can see command executed successfully, that means constraint has been removed on Studid column,  Lets ensures by inserting a zero value for StudId and it should allow zero value this time.

Lets try to insert zero value for StudId column now.

INSERT INTO Student (StudID)
VALUES (0)

You can see, records are inserted successfully, which ensures that check constraint has been deleted on column.

Lets select records from table.

 

 

SQL Server Check Constraint

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.