Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Add named default constraint to a populated table

Author  Topic 

WaterWolf
Starting Member

24 Posts

Posted - 2009-01-20 : 08:23:22
Hello,

I want to add a new column to an existing table in sql server 2005. The table is already populated with data.

It's possible to do this with the statement:

ALTER TABLE [MyTable] ADD [id] smallint NOT NULL DEFAULT 5

However, there's a problem with this because sql server will create a constraint called something like DF_MyTable_id_34F3C25A. The number at the the end seems to change randomly. If ever I need to drop or alter this column sql server will complain that I have to remove the constraint. However I need to know the name of the constraint to do this. As this database structure is going to be duplicated across many machines this is unfeasible.

It is possible to add a named constraint instead of using the syntax above however as my table is already populated this won't work. E.g.

ALTER TABLE MyTable ADD id smallint NOT NULL
ALTER TABLE MyTable ADD DEFAULT DF_mytable_id ((5)) FOR [id]

This will fail on the first line because there are already values in the table.

Is there a way of adding this named constraint to the table, or is there a way of deleting constraints on a field if you don't know the name of them ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 08:28:22
if the data is there u can't insert the column with not null
ALTER TABLE urtableadd id int constraint DF_mytable_id DEFAULT(5)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:32:02
yup its should be

ALTER TABLE MyTable

ADD id smallint NOT NULL

CONSTRAINT DF_mytable_id

DEFAULT 5 WITH VALUES
Go to Top of Page

WaterWolf
Starting Member

24 Posts

Posted - 2009-01-20 : 09:24:41
Yes, that syntax was just what I needed. Thanks !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:25:15
welcome
Go to Top of Page
   

- Advertisement -