[dba-SQLServer] RE: ADD Constraint

Hey Ken:

Not too sure if you can alter column and add a new constraint in a single 
ddl statement.

If you want to create a default constraint (assuming the column is already 
there), i'd use the following format:

ALTER TABLE [TableName] ADD CONSTRAINT [AddOwnerDflt] DEFAULT (0) FOR 
[ColumnName] with values;

If, for an existing table, you want to "create" a new column with a default 
in it, it'd look something like this:

ALTER TABLE [TableName] ADD yocolumnname datatype null CONSTRAINT 
[AddOwnerDflt] DEFAULT (0) with values;

HTH

Billy



>From: "Ken Stoker" <kens.programming at verizon.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] RE: ADD Constraint
>Date: Wed, 30 Mar 2005 11:19:37 -0800
>
>David,
>
>Thanks for your reply, but I had tried that already and received and error
>stating there was a syntax error near ADD.  Am I missing a comma or a
>bracket or something like that?
>
>Ken
>
>-----Original Message-----
>From: David Lewis [mailto:DavidL at sierranevada.com]
>Sent: Wednesday, March 30, 2005 10:22 AM
>To: dba-sqlserver at databaseadvisors.com
>Cc: kens.programming at verizon.net
>Subject: ADD Constraint
>
>
>Not sure, but I think you need ADD CONSTRAINT
>
>D. Lewis
>
>
>Message: 1
>Date: Tue, 29 Mar 2005 11:53:29 -0800
>From: "Ken Stoker" <kens.programming at verizon.net>
>Subject: [dba-SQLServer] Adding a default to an existing column
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <0IE400I33PSVKK00 at vms042.mailsrvcs.net>
>Content-Type: text/plain;	charset="windows-1250"
>
>I have put together the following to add a default to an existing table.
>It is telling me there is a syntax error near CONSTRAINT.  I have tried
>many different things, this being the latest, getting similar problems.
>
>
>
>
>ALTER TABLE TableName
>
>ALTER COLUMN ColumnName UserDefinedType NULL
>
>CONSTRAINT AddOwnerDflt
>
>DEFAULT '100000' WITH VALUES
>
>GO
>
>
>
>Thanks for any help.
>
>
>
>Ken
>
>--
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.8.6 - Release Date: 3/30/2005
>
>
>--
>No virus found in this outgoing message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.8.6 - Release Date: 3/30/2005
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>