Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment


Locations of visitors to this page


Latest Articles


Move to top
Different types of alter table command to change database schema
Posted By Sarin on Aug 19, 2012     RSS Feeds     Latest Hinduism news
3631 Views

The ALTER TABLE command is the part of the data manipulation language that allows you to change the schema of an existing table. It can also be used to add, change, or drop a column from an existing table. You can also change the data type of the columns and the constraints applied to that column.
  

  
Alter table: Change the size or data type of the column
Alter table UserInfo alter column UserName varchar(40)  
Where  
UserInfo is the tablename
UserName is the column name whose datatype you want to change to varchar(40)
Unlike oracle, sql server does not allow you to modify datatype or size of two or more columns in a single sql statement. You have to either recretae the table or write two statement to change datatype of columns in single statement.

Alter table: Drop two columns in one statement
alter table Subscriptions
       
drop column [Description], [Version]
Where  
Subscriptions is the tablename
Description and version are two columns of table you wanna drop  

Add 'check' constraint to it
alter table UserRole
add constraint ck_roles
check (roletype in  ('Admin', 'User', 'Anonymous'));
Where  
UserRole is the tablename
ck_roles is the Constraint name
roletype column name on which you want to apply list of roles  

Alter table to add a primary key
alter table UserRole add constraint pk_Roles primary key(RoleId)
Where  
UserRole is the tablename
pk_Roles is the Constraint name
RoleId column name which you want as primary key  

Alter table to add a foreign key with condition ON DELETE SET NULL
ALTER TABLE products
  
add CONSTRAINT fk_supplier
  
FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
 ON DELETE SET NULL;
Where  
products is the tablename
fk_supplier is the foreign key Constraint name
supplier_id, supplier_name list of column names which should have reference to column of another table  
supplier- name of the referenced table

Use alter table to add foreign key with condition cascade delete
ALTER TABLE products
add CONSTRAINT fk_supplier
  
FOREIGN KEY (supplier_id)
 REFERENCES supplier(supplier_id)
 ON DELETE CASCADE;
Where  
products is the tablename
fk_supplier is the foreign key Constraint name
supplier_id column name which should have reference to column of another table  
supplier- name of the referenced table

Alter table to rename column
The script for renaming any column:
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'
Ex:
sp_RENAME 'products.[productsid]' , '[productid]', 'COLUMN'
  
The script for renaming any object (table, sp etc)  
sp_RENAME '[OldTableName]', '[NewTableName]'
Ex:
sp_RENAME '[products]', '[product]'

Mark constraint with nocheck when creating a constraint
ALTER TABLE Customers
WITH NOCHECK
ADD
 CONSTRAINT CN_CustomerPhoneNo
CHECK
(Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
Where  
Customers is the tablename
CN_CustomerPhoneNo is the foreign key Constraint name
Phone column name which should have phone number pattern  as described in the statement

  
Note: Images used on this website are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.
Share on Google+ Share on Tumblr Print Browser Favorite
Related Articles
Get All Tables in a Database
create new controls in grid view at runtime
Get comma separated result of a database query
Loop through all records in Sql server with while statement
Change structures-constraints of table using alter command
Get list of all database objects with table name or column name
Different types of alter table command to change database schema
Delete duplicate records from table in SQL

Post Comment