SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Add named default constraint to a populated table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WaterWolf
Starting Member

24 Posts

Posted - 01/20/2009 :  08:23:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 01/20/2009 :  08:28:22  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/20/2009 :  08:32:02  Show Profile  Reply with Quote
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 - 01/20/2009 :  09:24:41  Show Profile  Reply with Quote
Yes, that syntax was just what I needed. Thanks !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/20/2009 :  09:25:15  Show Profile  Reply with Quote
welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000