You are here

ALTER COMMAND

alter command

ALTER COMMAND

In this post we will discuss about ALTER command it is useful when we have created a table and after we have to add or delete something from the table structure.

  • ALTER command is used to modify the structure of the exiting table.
  • Using ALTER command, it is possible to add or delete column, create or destroy indexes, change the data type of exiting columns, rename columns or the table itself, add or drop the constraints in SQL server.
  • It works by making the temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed.

To add a new column into table:

  • ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column.

Syntax:  
ALTER table <table_name> ADD col_name datatype(size);

To drop a column from the table:

  • The attempt to drop column fails when the column:
  1. Is used in an index.
  2. Is used in a default, check, foreign key, unique, or primary key constraint.
  3. Is bound to a default object or a rule.

Syntax:
ALTER table <table_name> DROP column col_name;

To modify the exiting column:

  • The attempt to alter column fails:
  1. When used in a primary key or foreign key constraint.
  2. When used in a check or unique constraint, unless you’re just keeping or increasing the length of a variable-length column.
  3. When used in default constraint, unless changing the length, precision, or scale of a column as long as datatype is not changed.

Syntax:
ALTER table <table_name> ALTER column col_name datatype;

To add constraint to column:

ALTER table <table_name> ADD constraint const_name constraint(col_name);

Note:

Here constraint is like primary key, foreign key, default, check.

To drop constraint to column:

ALTER table <table_name> DROP constraint const_name ;

To change database name:

  • We can change the database name by using either alter command or sp_renamedb Strored procedure.

Syntax(alter command):
ALTER DATABASE old_dbname MODIFY Name=new_dbname;
Syntax(stored procedure):
Exec sp_renamedb ‘old_dbname’, ‘new_dbname’;
Restrictions of the alter command:

  • Using alter command we cannot change the name of the table
  • Using alter command we cannot change the name of the column

sp_rename:

  • The sp_rename stored procedure allow us to rename table and column in SQL.

Syntax(to rename table):
Exec sp_rename ‘old_tablename’,’new_tablename’;
Syntax(to rename column):
Exec sp_rename ‘tablename.oldcol_name’,’tablename.newcol_name’,’COLUMN’;

Example:

Create a table name empaddress
create table empaddress(Name varchar(20));
Add a new column name city
Alter table empaddress ADD city varchar(10);
select * from empaddress;
output:

Name city

Add a new column name age
Alter table empaddress ADD age int;
output:

Name

city

age

Modify exiting column
Alter table empaddress Alter Column city nvarchar(20);
Rename a table name from empaddress to newtab
exec sp_rename ’empaddress’,’newtab’;
Rename a table back from newtab to empaddress
exec sp_rename ‘dbo.newtab’,’empaddress’;
Add new column id in to table
Alter table empaddress ADD id int not null;
output:

Name

city

age

id

Add constraint primary key in to table
ALTER TABLE empaddress ADD CONSTRAINT PK_id PRIMARY KEY(id);
select * from empaddress;
Insert one record into table
insert into empaddress(Name,city,age,id) values(‘riya’,’v.v.nagar’,21,1);
select * from empaddress;
output:

Name

city age

id

riya

v.v.nagar 21

1

Drop column name age from empaddress
Alter table empaddress DROP COLUMN age;
Select * from empaddress;
Output:

Name

city

id

Riya

v.v.nagar

1

Change column name city to cy
exec sp_rename ’empaddress.city’,’cy’,’COLUMN’;
select * from empaddress;
Output:

Name

cy id
Riya v.v.nagar

1

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

 

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top