Define DEFAULT constraints using CREATE TABLE:
-- Create a test table IF OBJECT_ID('Items') IS NOT NULL DROP TABLE Items GO CREATE TABLE Items ( -- no default constraint col1 INT, col2 INT NOT NULL, -- a value has to be entered when inserting a new row -- default constraint specified (there is no need to provide values for these rows) col3 INT DEFAULT 0, col4 INT NULL DEFAULT 0, col5 INT NOT NULL DEFAULT 0, ) GO -- Insert a few rows INSERT INTO Items(col1,col2) VALUES (1,2) INSERT INTO Items(col1,col2,col3) VALUES (1,2,3) INSERT INTO Items(col1,col2,col3,col4) VALUES (1,2,3,4) INSERT INTO Items VALUES (1,2,3,4,5) -- Show results SELECT * FROM Books col1 col2 col3 col4 col5 ---- ---- ---- ---- ---- 1 2 0 0 0 1 2 3 0 0 1 2 3 4 0 1 2 3 4 5
Add DEFAULT constraints using ALTER TABLE:
-- Example#1 ALTER TABLE Items ADD CONSTRAINT DF_Items_col1 DEFAULT (0) FOR col1 -- Example#2 ALTER TABLE Items ADD CONSTRAINT DF_Items_col6 DEFAULT ('') FOR col6 -- Example#3 ALTER TABLE Items ADD CONSTRAINT DF_Items_col7 DEFAULT (GETDATE()) FOR col7
Drop an unnamed DEFAULT constraint:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[wbs_DropDefault]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [wbs_DropDefault] GO -- A sproc to drop an unnamed DEFAULT constraint. CREATE PROCEDURE wbs_DropDefault @TableName SYSNAME, @FieldName SYSNAME AS BEGIN DECLARE @defname VARCHAR(100) DECLARE @cmd VARCHAR(1000) SET @defname = (SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = @TableName AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id(@TableName) AND name = @FieldName)) SET @cmd = 'ALTER TABLE '+@TableName+' DROP CONSTRAINT '+@defname EXEC(@cmd) END GO -- Drop a DEFAULT constraint EXEC wbs_DropDefault TableName, ColumnName GO