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 ?
Add named default constraint to a populated table
Posted - 01/20/2009 : 08:23:22
