July 10, 2007 at 11:08 am
Can someone please provide a solution to this problme.
I am adding a new column to a table using alter table.. This new column has a default say 0. Now if i want to rerun the same statement. I have to drop the column and rerun the add column statement again but thsi fails saying that a default is bind to this column. Now how will i have drop statment for default also.
if exists(select * from syscolumns where name='aa' and id = object_id(N'[dbo].[myclient]'))
alter table dbo.myclient drop column aa
GO
ALTER TABLE myclient ADD [aa] [bit] NOT NULL DEFAULT 0
Since constraint gets created with the alter statement how will we have a drop constraint before dropping the column.any suggestions willbe greatly appreciated. TIA
July 10, 2007 at 11:40 am
/* One way is to use the system tables to find the default constraint name if it exists, then use dynamic sql to drop it.
Of course, you could also add a named default constraint to begin with, then you know what the name is when you need to drop it.
declare @default varchar(100)
declare
@sql varchar(1000)
select
@default = object_name(cdefault)
from
syscolumns
where
object_name(id) = 'myclient'
and name = 'aa'
if
@default <> ''
begin
set @sql = 'ALTER TABLE dbo.myclient DROP CONSTRAINT ' + @default
exec (@sql)
end
Regards,
Rubes
July 10, 2007 at 1:26 pm
Are you scripting this? If this is just a one time occurrance then simply look at the error message and get the object name of the object that is dependent on the column you want to drop. Use that name in a seperate alter statement:
alter
table xxx drop constraint df__xxx___col1___3d5E1FD2
Then alter your table to drop the column. If this is something that occurs regularly then you should use named constraints:
create
table test2_ (col1_ int constraint df1 default 0, col2_ int)
James.
July 12, 2007 at 2:33 am
I agree with James - in fact will go beyond, I always make it a habit of naming the default constraints as this will help me a great deal in tracking them down in the future for general constraint maintenance like in your example, DROPPING the column with default constraint. In my opinion, I think it is a good practice too. Don't you think so?
In our development team, we have different projects running at different database level. As a nature in development, schema gets changed almost on hourly basis, and sometimes minutes! To ensure that we have the least impact to development cycle, I always have in my mind the RERUNNABLE approach. In your case, I would script it this way...
To add new column with Default
/*********************************************************
** Add column <COL_NAME> into table <TAB_NAME> if it does not exists.
*********************************************************/
If NOT EXISTS (Select * from syscolumns, sysobjects
Where syscolumns.id = sysobjects.id
And sysobjects.name = '<TAB>'
And syscolumns.name = '<COL_NAME>')
Begin
ALTER TABLE <TAB>
ADD <COLUMN> <DATATYPE>(<LENGTH> ) <NULL/NOT NULL>
CONSTRAINT df__<ABBREVIATED TAB NAME>__<COL_NAME> DEFAULT '<DEFAULT VALUE > '
End
Go
** Please note that I have purposely used the rerunability statement "IF NOT EXISTS..." - this method avoids DROPPING the existing database/table which may already have records and the developers need to keep for unit/xtesting. Applying a DROP COLUMN to this table in every single database may cause other deveoper to lose their data.
To DROP a column default constraint:
IF EXISTS ( select 1 from syscolumns c, sysobjects o
where o.id = c.cdefault
and c.name = '<TAB_NAME>
and o.xtype = 'D '
and object_name(c.id) = '<TAB_NAME>' )
BEGIN
Declare @<Default_constraint_name> VARCHAR(100)
SELECT @<Default_constraint_name> = (SELECT object_name(o.id)
from syscolumns c, sysobjects o
where o.id = c.cdefault
and c.name = '<TAB_NAME>'
and o.xtype = 'D '
and object_name(c.id) = '<TAB_NAME>' )
EXECUTE ('ALTER TABLE <TAB_NAME> drop constraint ' + @<Default_constraint_name> )
END
Go
The only only downside is if there is a change to the column e.g. column name, datatype, null, etc. which you will have to make the adjustment where necessary. Although the above is rather long-winded but once scripted, it can be rerun as many times as you like. Our QA team loves this as they can be sure that whatever test data they input will not be gone even if they have to re-apply the entire database upgrade script.
Herb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply