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.
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 5However, 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 NULLALTER 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) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 08:32:02
|
yup its should beALTER TABLE MyTableADD id smallint NOT NULLCONSTRAINT DF_mytable_idDEFAULT 5 WITH VALUES |
|
|
WaterWolf
Starting Member
24 Posts |
Posted - 2009-01-20 : 09:24:41
|
Yes, that syntax was just what I needed. Thanks ! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 09:25:15
|
welcome |
|
|
|
|
|