Tuesday, 22 October 2013

What is the SQL Alter Command to Modify Table Structure

SQL Alter Command
Alter is DDL command is used to modify the structure of the schema after the creation.
Suppose we have an Employee table with three columns Emp_id, Emp_name, Emp_salery. Now let's use the alter commands to modify the Employee table structure.

By using Alter command we can do the following operation in SQL Server:
Add Column - Adds a column to a table
Add Primary Key - Adds a primary key to a table
Add Foreign Key - Adds a foreign key to a table
Add Constraint - Adds a check constraint to a table
Change Column Type - Changes the data type of a column
Drop Column - Drops a column from a table
Drop Constraint - Drops a check constraint from a table
Drop Primary Key - Drops a primary key from a table
Drop Foreign Key - Drops a foreign key from a table
Rename Table - Renames a table
Rename Column - Renames a column

Examples :
Adding a Primary Key Constraint on an existing Column

ALTER TABLE Employee  ADD PRIMARY KEY(Emp_id) 
or
ALTER TABLE Employee  ADD CONSTRAINT Emp_id_pk PRIMARY KEY(Emp_id) 

Droping a Constrint
ALTER TABLE Employee DROP CONSTRAINT Emp_id_pk;

Adding a Foreign Key Constraint on an existing Column
Let us suppose Orders and Employee are two tables…
ALTER TABLE Orders  ADD FOREIGN KEY (Emp_id) References Employee(Emp_id)
or
ALTER TABLE Orders  ADD CONSTRAINT Emp_id_fk FOREIGN KEY (Emp_id) References Employee(Emp_id)

Increasing or Decreasing the size of an existing column
ALTER TABLE Employee  ALTER COLUMN Emp_name varchar(100) 

Changing the Data Type of an existing Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) 

Adding  a NULL Constraint on Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NULL 

Adding a NOT NULL Constraint on an existing Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NOT NULL 

Adding a new Column on Table without any Constraint
ALTER TABLE Employee  ADD Emp_age varchar(10) 

Add a new Column on Table with a Constraint
ALTER TABLE Employee  ADD Emp_code varchar(100) CONSTRAINT Emp_code_uq UNIQUE 

Droping a Column from Table
ALTER TABLE Employee DROP COLUMN Emp_code
 
Adding a Check Constraint Column
ALTER TABLE Employee ADD CONSTRAINT Emp_age_ck CHECK(Emp_age > 18) 

Notes :
PRIMARY KEY CONSTRAINT cannot be altered, you may only drop it and create again. For big datasets it can cause a long run time and thus - table inavailability.


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts