Different types of alter table command to change database schema
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.
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