Adding a default constraint to an existing column

[Stan]

I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :

ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)

This gives me a syntax error.

The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.

Anyone have any ideas? Thanks in advance.

easiest to do in enterprise manager.

just open the table in design mode and type (0) in the default column.
takes a while to complete, especially with 2.6 million rows but it does the job.

of course this won't reset any values but seems like you know that already.

------------
Stan at 3/9/00 5:13:54 PM

I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :

ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)

This gives me a syntax error.

The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.

Anyone have any ideas? Thanks in advance.

[Stan]

Yes. I was trying to avoid the Enterprise Manager route. I only want to add a new constraint that will not affect the existing 2.6 millions rows. The should be a way to script this.

If I add (0) to the default column in EM, it will transfer ALL 2.6 million rows to a temp table and back again to complete that change. This takes way too long.


------------
at 3/9/00 5:43:05 PM



easiest to do in enterprise manager.

just open the table in design mode and type (0) in the default column.
takes a while to complete, especially with 2.6 million rows but it does the job.

of course this won't reset any values but seems like you know that already.

------------
Stan at 3/9/00 5:13:54 PM

I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :

ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)

This gives me a syntax error.

The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.

Anyone have any ideas? Thanks in advance.