03.15.07

Add & remove columns & constraints to tables

Posted in DBA, Microsoft Sql server, TSQL at 9:13 pm by

–Sample Making a column not null and make it default to a value
–When the table has been in use and the existing values shouldn’t change.
update test1 set code = 0 WHERE code is null;
alter table test1
alter column code tinyint not null;
ALTER TABLE test1 ADD CONSTRAINT DF_test1_code DEFAULT ((0)) FOR code;

–Sample Adding a column with a default value
ALTER TABLE test1
ADD code tinyint not null
CONSTRAINT DF_test1_code DEFAULT ((0));

–Removing a constraint
ALTER TABLE test1
DROP CONSTRAINT DF_test1_code;

–Removing a column
ALTER TABLE test1
DROP column code;

Leave a Comment