You are here
Home > Sql Server > ALTER COMMAND in sql

ALTER COMMAND in sql

ALTER COMMAND

  • It is used to modify the structure of the exiting table.
  • Using ALTER command, it is possible to rename columns or the table itself, add or delete column, change the data type of exiting columns, add or drop the constraints in SQL server.

 

adding a column:

  • ALTER TABLE allows columns to be added that can contain nulls, or the column being added is an identity, or have a DEFAULT definition specified, or timestamp column.
  • Syntax:
  • ALTER table <tablename> ADD column_name datatype(size);

dropping a column:

  • The attempt to drop column fails when column:
    1. Is used in an index.
    2. Is bound to a default object or a rule.
    3. Is used in a foreign key, default, check, unique, or primary key constraint.
  • Syntax:
  • ALTER table <tablename> DROP column <column_name>;

To modify the existing column:

  • The attempt to alter column fails:
  1. When used in a foreign key or primary key constraint.
  2. When used in a check or unique constraint, unless you are just keeping or increasing length of a variable-length column.
  3. When used in default constraint, unless changing the length, precision, or scale of a column if datatype is not changed.
  • Syntax:
  1. ALTER table <tablename> ALTER column column_name datatype;

To add constraint to column:

  • Syntax:
  • ALTER table <tablename> ADD constraint const_name constraint(columnname);

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

To drop constraint to column:

  • Syntax:
  • ALTER table <tablename> DROP constraint const_name;

To change database name:

  • We can change database name by using either sp_renamedb or alter command.
  • Syntax (stored procedure):
  • Exec sp_renamedb ‘old_dbname’, ‘new_dbname’;
  • Syntax (alter command):
  • ALTER DATABASE old_dbname MODIFY Name=new_dbname;

 

Example:

  • Create a table demo.
  • create table demo(Name varchar(20));
  • Add a new column name State in to demo table.
  • Alter table demo ADD State varchar(20);

    select * from demo;

    output:

    Name

    State

     

  • Add a new column name City in to demo table.
  • Alter table demo ADD City varchar(10);

    select * from demo;

    output:

    Name

    State

    City

     

  • Modify existing column City.
  • Alter table demo Alter Column City nvarchar(20);

     

  • Add new column Id in to demo table.
  • Alter table demo ADD Id int not null;

    select*from demo;

    output:

    Name

    State City

    Id

     

  • Add constraint primary key in to demo table.
  • ALTER TABLE demo ADD CONSTRAINT PK_id PRIMARY KEY(Id);

    select * from demo;

    output:

    Name

    State City

    Id

     

  • Insert record into demo table.
  • insert into demo(Name,State,City,Id) values(‘Krishna’, ‘Gujarat’,’V.V.Nagar’,1);

    select * from demo;

    output:

    Name

    State City Id
    Krishna Gujarat V.V.Nagar

    1

     

  • Drop column City from demo table.
  •  Alter table demo DROP COLUMN City;

    select * from demo;

    output:

    Name

    State

    Id

    Krishna

    Gujarat

    1

     

     

     

     

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

 

Leave a Reply

Top