ALTER Statements in SQL Server

Today we will learn about the SQL alter statements and look into scenarios in which we can use them and how.So here we go

To create table use the following.

    CREATE TABLE [dbo].[person](

      [PERSON_ID] [varchar](20) NOT NULL,

      [PERSON_NAME] [varchar](15) NULL,

      [PERSON_ADDRESS] [varchar](20) NULL,

      [Department] [varchar](20) NULL
)

To change the table name and column name, use the following:

 1.                    -- To change table name

 sp_rename 'person_table','person'

 2.                    -- To change column name

 sp_rename 'person.designation','Department','column' 

 

To alter tables use the following queries:

3.                    -- To Add a column

 alter table person

 add Designation varchar(20)

4.                    -- To Drop a column

 alter table person

 drop column designation 

5.                    -- To change data type of column

 alter table person

 alter column person_id int 

6.                   -- To Add primary key constraint

 alter table person

 add constraint pk_person_id primary key(person_id)

7.                    -- To Drop primary key constraint

 alter table person

 drop constraint pk_person_id

8.                    -- To Add foreign key constraint

 alter table person

 add constraint fk_person_id foreign key(person_id) references department(person_id)

9.                    -- To Drop foreign key constraint

 alter table person

drop constraint fk_person_id