February 23, 2015 at 2:36 pm
I scripted out some constraints from a database (SQL2012) and got something similar to the following:
USE [Databasename]
GO
ALTER TABLE [dbo].[tablename] ADD DEFAULT ('') FOR [columnname]
GO
This construction (which I assume sets the default value of a column to "") doesn't seem to be described in BOL. The word CONSTRAINT isn't there and there is no name assigned. They seem to have system generated names. If that is the case, then we can run them on a copy of the DB on another system and not worry that they'll have slightly different names, right?
Dan
February 23, 2015 at 3:04 pm
You're right: the constraint name is system generated, so it won't likely be the same on different databases. It's a best practice to name constraints to avoid fighting with different names in different databases.
Another way to obtain the same result is declaring the default inline with the table definition:
CREATE TABLE foo (
bar int DEFAULT 1
)
Again, you'll get a system generated name for the constraint.
-- Gianluca Sartori
February 23, 2015 at 3:08 pm
If no explicit constraint name is specified, then SQL Server will assign a random one at the time of object creation. It's best supply a constraint name so it's consistent across deployments, not just for documentation purposes, but also in case you later need to write a script that drops or alters the constraint. For primary keys, check constraints, and foreign keys, it again helps to have given names so that error messages are more descriptive.
create table MyTable
(
col1 int not null constraint pk_MyTable primary key,
col2 int not null,
col3 varchar(30) not null constraint df_MyTable_col3 default (''),
col4 char(1) not null
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 23, 2015 at 4:04 pm
It is described in BOL. Under "ALTER TABLE", "table constraints". Notice that the text "[CONSTRAINT constraint_name]" is optional, followed by the required "DEFAULT constant_expression FOR column". Viz:
ALTER TABLE table_name
ADD <table_constraint>
<table_constraint> ::=
[ CONSTRAINT constraint_name ]
{
DEFAULT constant_expression FOR column [ WITH VALUES ]
}
Btw, the constraint name is not really "random", just a small part of it, as you can tell by looking at the name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 23, 2015 at 5:24 pm
Allow me to ask a dumb question or three.
Why would you create a constraint like that in the first place?
What is the NULLable property of the column?
Why not just store NULL when no value is specified and treat it accordingly?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 24, 2015 at 9:56 am
This database was developed in-house several years ago by developers who no longer work here. Yes, I suspect the table was designed in the GUI and they just typed the default value into "Default Value or Binding."
I have recommended to our developer that the database recreation script include commands to set names for these constraints.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply