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 constraint and remove an existing constraint on table.
Let’s look at an example, how to use 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.
SELECT * FROM Student
Let’s see all the operations that you can perform using alter command.
ALTER TABLE ADD COLUMN
It is used to add a new column in an existing table.
SYNTAX
Alter table table_name add 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 table table_name alter 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, let’s see 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)
Let’s check, whether the size of column is changed to VARCHAR(20) or not.
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 table table_name drop 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 constraint 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 constraint 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 can 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 than 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 constraint 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.
Let’s 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.
Also Read..
