Change structures-constraints of table using alter command
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
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.
Related Articles
Get All Tables in a Database
Create property class of database table
Change structures-constraints of table using alter command
Apply Multiple Order by on a list using LINQ
Different types of alter table command to change database schema
Post Comment