This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
Working with Default Constraints
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
Often it's useful to store table definitions as text file scripts, for purposes of documenting a database or as part of a disaster recovery plan. You can use SQL Server's 7.0 Enterprise Manager to script out a table by right-clicking over the table name and choosing "All Tasks," and then "Generate SQL Scripts" from the menu. If you don't choose any special options, and then click the Preview button, you'll see a CREATE TABLE statement to recreate the table, but you won't see the default constraints listed in the statement. To see the column defaults, you'll have to click on the Options tab and check the "Script PRIMARY Keys, FOREIGN keys, Defaults, and Check Constraints" checkbox. Unfortunately, the scripting utility puts the default constraints at the end in an ALTER TABLE statement. For the previous table, we see the following:
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
You can use gp_TableScript stored procedure (available in the accompanying Download file) to generate a script that will use the ANSI COLUMNS view to extract all of the information necessary to recreate a table, and place the column defaults in the CREATE TABLE statement. Because a CREATE TABLE statement might easily be larger than 8,000 bytes, the stored procedure just uses PRINT to generate the script into the output window of Query Analyzer. After generating the initial boilerplate part of the CREATE TABLE, gp_TableScript sets up a WHILE loop to iterate through the COLUMNS view. As it loops through the view, it takes each column, in column order, and prints out the appropriate statement.
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:
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:
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
Default constraints can come back to bite you when you drop a column. For instance, attempting to drop the cust_name column from the Customers table in the previous example:
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.
*Ron Talmage is a independent database consultant with Prospice, LLC. Ron is author of Microsoft SQL Server 7.0 Administrator's Guide (Prima Publishing), is a SQL Server MVP, the PASS newsletter editor, and is current president of the Pacific Northwest SQL Server Users Group. RonTalmage@compuserve.com.*
To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the January 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.