User Tools

Site Tools


notes:sql:default

DEFAULT constraint

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
notes/sql/default.txt · Last modified: 2020/08/26 (external edit)