NOT NULL WITH DEFAULT - PSULionR

I want to Default a column to "No" if nothing is entered for it. How can I
define the table and column? Do I need to use a CONSTRAINT here?

CREATE TABLE [dbo].[TableName] (
[Answer] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL WITH
DEFAULT 'No'

I know this syntax does not work...just looking for a little help here.

Thanks in advance for your help.

PSULionRP

NOT NULL WITH DEFAULT - Plamen Ratchev

Yes, you need to use a DEFAULT constraint:

CREATE TABLE TableName (
answer VARCHAR(3) NOT NULL
CONSTRAINT df_answer
DEFAULT 'No');

--
Plamen Ratchev
http://www.SQLStudio.com

NOT NULL WITH DEFAULT - PSULionR

Thanks Plamen. What's the meaning behind "df_"???

NOT NULL WITH DEFAULT - Jason Folkens

An alternate syntax that I like to use:


CREATE TABLE awesome_things

(

pkid INT IDENTITY,

descr VARCHAR(100) NOT NULL DEFAULT ('')

)

NOT NULL WITH DEFAULT - Tibor Karaszi

A default is implements in SQL Server as a constraint, and constraints are named. So this is the
name of the default constraint. For instance if you want to remove it you do:

ALTER TABLE TableName DROP CONSTRAINT df_answer


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

NOT NULL WITH DEFAULT - Aaron Bertrand [SQL Server MVP]

The problem here is you need to go chasing down the name of the constraint
in the system catalogs if you ever want to drop it and/or change it.  It can
also be painful if you are trying to minimize differences between, say, a
test environment and production... depending on how you created the
constraint in both places, these can add noise to the signal when running
comparison scripts.

If you use df_answer you have one less step.  I am often lazy like you, but
in important systems, best practice is to name the constraint logically
instead of letting the system come up with some random name.

NOT NULL WITH DEFAULT - Aaron Bertrand [SQL Server MVP]

It's just a name prefix to help immediately identify the TYPE of constraint
(df = default, uq = unique, etc).


On 8/27/08 11:59 AM, in article
F7731096-C114-4C1F-B2A9-25B282BBE74D@microsoft.com, "PSULionRP"

NOT NULL WITH DEFAULT - Jason Folkens

Try not to call people lazy when you offer them advice.    Its slightly less
abrasive. :-)

We use SQL Compare to compare our production and QA databases, so the
cryptic autogenerated name doesn't cause a problem.

Jason

NOT NULL WITH DEFAULT - David Portas

I'm interested. How do you deploy changes to defaults? If your defaults use
randomly assigned system names then I find the only way is to write some
very awkward code to script the change from system tables. (see below)

You could use SQL Compare to generate deployment scripts but that means
you'll need different versions of scripts for deploying to QA and to
production, which is not the configuration management practice in many
shops. Normally I want to create one parameterised script and deploy that
same script to QA, Staging and Production. If you rely on generated scripts
between each environment then there are certain risks that things will go
awry.

I'm interested because I find myself cursing the guilty developers every
time I discover system-named constraints in a database and then have to
write nasty workarounds such as this.

DECLARE @vDrop NVARCHAR(1000);

SELECT @vDrop =
'ALTER TABLE '
+ QUOTENAME(SCHEMA_NAME(d.schema_id))
+ '.'
+ QUOTENAME(OBJECT_NAME(d.parent_object_id))
+ N' DROP CONSTRAINT '
+ QUOTENAME(d.name)
+ '; '
+ N'ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT df_tbl1_x DEFAULT (0) FOR x ;'
FROM sys.default_constraints d
JOIN sys.columns c
ON c.column_id = d.parent_column_id
AND c.object_id = d.parent_object_id
WHERE parent_object_id = OBJECT_ID('tbl1')
AND schema_id = SCHEMA_ID('dbo');

EXEC (@vDrop);

--
David Portas

NOT NULL WITH DEFAULT - Jason Folkens

We typically have two versions of the database

a) the old version of the database.   This will be the database as it is in
our QA environment prior to deployment
b) the newer version of the database.  This is the one that we develop
against.

Our developers generally don't keep track of the changes that they make to
the database.  When it comes time to deploy the application to QA, we run
sql compare against the two databases.   the resultant script is what we
bake into the MSI as our schema update script.

We've never had any versioning difficulties, so its feasable that we have a
problem and it's just never been brought to my attention....    but I
thought sql compare DOES make the comparison on the auto-generated name
instead of using the same nameless syntax that we initally used to create
the default...

if not by default, then its one of the configuration options.

NOT NULL WITH DEFAULT - David Portas

You are right but then you are actually doing exactly what Aaron suggested
and what I would suggest - you are scripting the names and (presumably)
checking them into source control or config management using a SPECIFIC name
rather than rely on the system to generate one. It's just that you'll be
using unfriendly impossible-to-remember names whereas I prefer a naming
convention that I'll know by heart.

--
David Portas

NOT NULL WITH DEFAULT - --CELKO--

CREATE TABLE[TableName
( answer CHAR(3) DEFAULT 'No' NOT NULL
CHECK (answer IN ('No ', 'Yes', 'N/A', 'UA'),
.. );

1)  In Standard SQL, the DEFAULT clause comes after the data type.
Most products are forgiving about where you actually place it, but
follow standards so people will think you are a real SQL programmer
and not a hack.
2) Don't use VARCHAR(n) for small values of (n).  It makes formating
easier and saves some minor overhead.
3) Be sure that the DEFAULT value is the same data type as the
column.  Did you notice that I put an extra on the end of 'No ' to
fill it out to CHAR(3)?
4) Be sure that the DEFAULT value is in the domain of the column.
Sounds silly, but you would be surprised.