Home→Blogs→

Display any content here, from text, images, to rich HTML. Use the close link to dismiss the box. Click the close box to dismiss it.

Hide Box

Change structures-constraints of table using alter command

Posted By admin on Jul 14, 2012 165 Views

Latest Hinduism news

You many be probably aware of the various altercation we can do using alter statements. However, I observed that many developers are not aware of the simple, yet powerful feature of alter statement. In this article, I have encapsulated almost all functionalities of alter statement.
The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table. It also allows reassigning partitions, or disabling or enabling constraints and triggers.

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

PDF files are not generated for latest articles.

Tags: Alter,Sql,Table,Constraints,Columns,Delete,Roles

Share this to your friends. One of your friend is waiting for your share.

All content of this website(Except Blogs) is handled by an automated process and is subjected to errors.