Working with Default Constraints

Ron Talmage

There’s something bothersome about the way SQL Server Enterprise Manager scripts out tables. The default constraints are listed after the CREATE TABLE statement, and the default constraints are named–something most of us would never do. In this month’s column, Ron Talmage shows how you can generate your own script for creating a table that places default constraints inside the CREATE TABLE statement, giving it a more natural look than Enterprise Manager’s.

Default constraints are a special case of column defaults. A column default is some value or function that the column will take when an INSERT statement doesn’t explicitly assign a particular value. In other words, the column default is what the column will get as a value by default. In T-SQL, there are really two kinds of column-level defaults: default constraints and bound defaults.

Default constraints are ANSI-standard constraints you can assign to a column either with the CREATE TABLE or ALTER TABLE statements. Although default constraints have names, the CREATE TABLE statement doesn’t let you do it–you have to let SQL Server construct a name. For example, the following CREATE TABLE statement creates a default for the cust_name column, and doesn’t name it:

CREATE TABLE dbo.Customers (
  cust_id INT NOT NULL PRIMARY KEY,
  cust_name VARCHAR(30) NOT NULL DEFAULT 'new customer',
  last_updated_by sysname)

The last_updated_by column doesn’t have a default, so we can add it with the ALTER TABLE statement, but with ALTER TABLE, we must name the constraint:

ALTER TABLE dbo.Customers
  ADD CONSTRAINT def_last_updated_by 
  DEFAULT SUSER_SNAME()
  FOR last_updated_by

In general, when you don’t name a default constraint, SQL Server will assign one. In the first example, SQL Server assigned DF__Customers__cust___151B244E as the default constraint name for cust_name–not terribly elegant.

Bound defaults are independently defined with the T-SQL CREATE DEFAULT statement. You can then bind them to columns or to user-defined data types using the system stored procedure sp_bindefault and remove them with sp_unbindefault. An advantage of bound defaults is that you can define a bound default once and then bind it to many columns. The disadvantages are that bound defaults aren’t ANSI standard (they’re leftover from Sybase days) and that using system stored procedures rather than true DDL to manage them is somewhat awkward. I’d recommend that you avoid them and use ANSI standard default constraints instead.

You probably know that, in the absence of a default constraint (or a bound default), SQL Server will attempt to insert NULL into a column that isn’t otherwise provided a value in the INSERTstatement. For that reason, column defaults are very useful when combined with columns that don’t allow NULL. If you define a column as NOT NULL, but give the column a default, then INSERT statements don’t need to explicitly assign values to the column in order to insert a new row.

The scripting problem

CREATE TABLE [dbo].[Customers] (
  [cust_id] [int] NOT NULL ,
  [cust_name] [varchar] (30) NULL ,
  [last_updated_by] [sysname] NOT NULL 
)
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 
  CONSTRAINT [DF__Customers__cust___151B244E] 
  DEFAULT ('new customer') FOR [cust_name],
  CONSTRAINT [def_last_updated_by] DEFAULT 
  (suser_sname()) FOR [last_updated_by],
  PRIMARY KEY  CLUSTERED 
  ([cust_id])  ON [PRIMARY] 
GO

There are a number of problems here. First of all, the default constraints are named and placed after the CREATE TABLE statement in an ALTER TABLE command. Most people, including myself, just put default constraints inline, in the CREATE TABLE statement. Consequently, the scripted table definition often won’t match the original statement. This can be problematic. If you want to create a copy of that table in the same database with the same column defaults, you’ll have to change those constraint names–in an editor–one by one. A further problem is that the scripting utility isn’t configurable. For example, if you remove the square brackets around column names, then the Query Analyzer can help pinpoint column names that are SQL keywords–something I like to check for. To remove those brackets, you’ll have to edit them out yourself.

ANSI views to the rescue

In the code the @cmd is loaded first with the column name:

SET @cmd = (SELECT CHAR(9) + 
SUBSTRING(t1.column_name,1,60) + CHAR(9) 

then with the data type:

RTRIM(t1.data_type) 

Unfortunately, the view won’t tell us whether the column has the identity property, so the stored procedure uses the COLUMNPROPERTY() function instead:

CASE WHEN COLUMNPROPERTY(OBJECT_ID(t1.table_name), 
t1.column_name, 'isidentity') = 1 
THEN ' identity' ELSE '' END

Then if the column is a character, get its length:

CASE WHEN t1.data_type IN('char', 'varchar', 
'nchar', 'nvarchar') THEN 
'(' + RTRIM(character_maximum_length) + ')'

Or if the column is numeric, get its precision and scale:

WHEN t1.data_type IN('decimal', 'numeric') 
THEN '(' + RTRIM(numeric_precision) + ',' + 
RTRIM(numeric_scale) + ')'
ELSE '' END

Then add whether the column should be NULL or not:

CASE WHEN Is_Nullable = 1 THEN 'NULL' 
ELSE 'NOT NULL' END

Finally, get the column default constraint value from the view:

CASE WHEN DATALENGTH(t1.column_default) > 0 
THEN 'DEFAULT ' + t1.column_default ELSE '' END

When you use the stored procedure against the preceding table, like so:

gp_TableScript 'customers'

the output of the stored procedure is

IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers
go
CREATE TABLE dbo.Customers(
  cust_id int NOT NULL,
  cust_name varchar(30) NOT NULL DEFAULT 
  ('new customer'), last_updated_by nvarchar(256)
  NOT NULL DEFAULT (suser_sname())

The stored procedure inserts tabs but can’t make the columns line up precisely. I use it most often to generate scripts for documenting table definitions where column defaults are important. You can customize it further to add square brackets if you want, and to detect bound defaults or check constraints.

Dropping a default constraint

ALTER TABLE Customers
DROP COLUMN cust_name

fails because the default constraint hasn’t been removed. When you use the visual data tools in SQL Server to remove the column, that’s all taken care of for you, but when you’re writing your own T-SQL, this can be a stumbling block. Unfortunately, the name of the column default constraint isn’t kept in the ANSI COLUMNS view, so you must go back to the system tables to find the name. One way to do it is to use sysobjects to get the default constraint names for the table, joined with sysconstraints on the constraint id, limited by the appropriate column id from syscolumns. Then we need to embed it into an ALTER TABLE statement so that the default constraint will be dropped. Here’s an example of that approach:

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname = 
(SELECT name 
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid 
WHERE object_name(so.parent_obj) = ‘Customers’ 
AND so.xtype = ‘D’
AND sc.colid = 
 (SELECT colid FROM syscolumns 
 WHERE id = object_id(‘dbo.Customers’) AND 
 name = ‘cust_name’))
SET @cmd = ‘ALTER TABLE Customers DROP CONSTRAINT ‘
+ @defname
EXEC(@cmd)

You could also make this a stored procedure to drop a particular column on a table. However, while this handles default constraints, it doesn’t handle all types of constraints. Bound defaults, check constraints, and foreign key constraints can all prevent an ALTER TABLE from dropping a column.

Download RON100.SQL