Default Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

Each column in a table can contain one DEFAULT definition.

DEFAULT definitions cannot be created on columns defined with the following:

• A timestamp data type.
• A sparse column, because a sparse column must allow null values.
• An IDENTITY or ROWGUIDCOL property.
• An existing DEFAULT definition or DEFAULT object.

The default value must be compatible with the data type of the column to which the DEFAULT definition applies. For example, the default value for an int column must be an integer number, not a character string.

When a DEFAULT definition is added to an existing column in a table, by default, the Database Engine applies the new default only to new rows of data that are added to the table. Existing data that was inserted by using the previous DEFAULT definition is unaffected. However, when you add a new column to an existing table, you can specify that the Database Engine insert the default value (specified by the DEFAULT definition) instead of a null value, into the new column for the existing rows in the table.

When you delete a DEFAULT definition, the Database Engine inserts a null value instead of the default value when no value is inserted into the column for new rows. However, no changes are made to the existing data in the table.

While Creating:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes')

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) Constraint constraint_name DEFAULT 'Sandnes'
)

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE())

Altering existing column

             ALTER TABLE test add default ‘SANDNES’ for city                        

                      OR

             ALTER TABLE test add constraint constraint_name default 'SANDNES' for city

Adding a new column:

ALTER TABLE [table name] ADD

    [New Column Name] [Column Type]

    CONSTRAINT [constraint name] DEFAULT ([default value]) NOT NULL,

 

Drop default constraint:

ALTER TABLE Persons DROP CONSTRAINT <<Constraint_Name>>

You can’t modify a default constraint you should drop and re-create it.

This is all well and good if your DEFAULT constraints are named, but this may not always be the case.If a DEFAULT is defined inline within a table without a name, a unique name will be auto-generated by the SQL Server database engine. This unique name is a bit ugly and hard work with. If you maintain multiple databases, this auto-generated name will be different from database to database. As a result, you cannot rely on using an an auto-generated name statically within a static DROP CONSTRAINT statement. In these cases, the only way to modify DEFAULT constraints with auto-generated names is to capture the name from SQL Server's system catalog and to run a dynamic SQL query to perform the DROP.

You should note that SQL Server's INFORMATION_SCHEMA views include views that report on database constraints, however Default's cannot be found within them. As mentioned earlier, the ANSI-SQL standard defines constraints as a column property. Since the INFORMATION_SCHEMA views adhere to ANSI-SQL standards and DEFAULT's are not an ANSI-SQL defined type of constraint, they're not included within these views. However, you can easily find information about a column's DEFAULT constraint within the following system table catalogs:

SQL Server 2000: sys.columns
SQL Server 2005: sys.default_constraints

My recommendation is to use INFORMATION_SCHEMA views whenever possible when working with database objects and to avoid reading system tables whenever you can.

My reasoning is that the SQL Server system catalogs can be (and have been) changed from release to release. In addition, some of these catalogs could be removed entirely from SQL Server. In SQL Server 2005, some of the well known SQL Server 2000 system catalog tables such as sysobjects and sysindexes are kept around as backward compatible views, but they may be gone by the time SQL Server 2008 is finally released. In some cases, reading the system catalogs is unavoidable. In these cases, make sure you document where you're reading system tables directly.

Furthermore, I also recommend naming ALL database objects regardless if they're PRIMARY KEY, UNIQUE, CHECK, DEFAULT, etc. Explicitly naming all database objects eliminates the need to read system catalogs if you someday need to update these objects and also better documents your database.

“sp_helpconstraint test” will list out all the constraints defined on the table.

• How can we check default constraint defined on specific column:

                  select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS where                           TABLE_NAME ='persons' and COLUMN_NAME = 'City'

        If column_default is not null then column has default constraint.

                 OR

                 select * from sysconstraints where id = OBJECT_ID('persons')
                 and OBJECTPROPERTY(constid,'isdefaultcnst')=1
                 and COL_NAME(id,colid) ='city'

• How can we get default constraint name defined on specific column:

select dc.name

from sys.tables st

INNER JOIN sys.default_constraints dc ON st.object_id = dc.parent_object_id

INNER JOIN sys.columns sc ON st.object_id = sc.object_id

and sc.column_id = dc.parent_column_id

where st.name ='persons'

and sc.name ='city'

• How do I drop default constraint defined on specific constraint(don’t know the name of constraint):

Will give use the default name, in this case it is DF__Persons__City__2C3393D0
So now we can drop the default by doing this

ALTER table foo2 drop DF__Persons__City__2C3393D0

So what is the big deal you say?

Let’s say you do this on a staging box and want to create a script to hand over to someone else who will run it on the production box
If you create you script on the staging box and the person runs it on production he will see something like this

erver: Msg 3733, Level 16, State 2, Line 1
Constraint ‘DF__Persons__City__2C3393D0’ does not belong to table ‘city′.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.

Then you will get a call that your script is broken, you will tell him that it works on Development environment. In the end you will have to do something like this so that it can run on any server as long as the table and column name are the same.

declare @table_name nvarchar(256)

declare @col_name nvarchar(256)

declare @Command  nvarchar(1000)

set @table_name = N'Persons'

set @col_name = N'city'

select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' +   d.name

 from sys.tables t  

 join    sys.default_constraints d on d.parent_object_id = t.object_id 

 join    sys.columns c on c.object_id = t.object_id     

 and c.column_id = d.parent_column_id

 where t.name = @table_name

 and c.name = @col_name

exec @command