T-SQL to add a column bound to a existing default constraint

Question

I have a DEFAULT constraint created in the database, which I want to share among several tables, and I want to script an unique alter table statement for adding a field in that tables as follows:

ALTER TABLE MyTable ADD MyField NOT NULL DEFAULT(DF_EXISTING_DEFAULT)

The following error ocurrs:

Msg 128, Level 15, State 1, Line 1
The name "DF_EXISTING_DEFAULT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Notice that the default value is already defined in DF_EXISTING_DEFAULT, I don't want to write it again. How can I do it? Any reason I shouldn't do it? Is it a t-sql limitation?

Martin Smith · Accepted Answer · 2013-12-17 13:10:21Z

4

You actually can do it but the mechanism to do so is deprecated so shouldn't be used.

You can CREATE DEFAULT object and then use sp_bindefault.

There is no non deprecated replacement. If the logic for the default is complex you could put it in a scalar UDF to avoid repeating yourself.

answered Dec 17, 2013 at 13:10

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

natenho Over a year ago

Well, the default is not complex (e.g. DEFAULT('A simple text'), but it can change in the future. In this case, is UDF the only way to avoid that "code duplication"? The main objective at this moment is to keep a single default and to avoid those sql automatic constraint naming.

Martin Smith Over a year ago

@natenho - Yes. A default constraint can't be shared between columns and there are no user defined global variables in SQL Server so to avoid repeating 'A simple text' in the constraint definitions themselves this is the only way. You could also store it in a table but that would also require a UDF to access. The advantage of storing in a table might be easier maintenance (altering the UDF definition would require dropping all defaults referencing it) but hardcoded in the UDF would be more efficient and in constraint definition itself more so. So trading performance vs maintainability.

natenho Over a year ago

Regarding "can't be shared"...when you use sp_binddefault for the same DF in multiple table/columns, aren't you sharing the constraint? At least I can't see new constraints being created.

Martin Smith Over a year ago

@natenho - That is sharing a default object not default constraint. Trying to bind a constraint gives Cannot bind default 'X'. The default must be created using the CREATE DEFAULT statement. (at least on my version of SQL Server)

Martin Smith Over a year ago

Yes that's a (deprecated) default object. Not a (non deprecated) default constraint.

|

Collectives™ on Stack Overflow

T-SQL to add a column bound to a existing default constraint

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related