Systems Engineering and RDBMS

Dropping a column with a default constraint

Posted by decipherinfosys on April 3, 2009

In many IT shops, we have seen that proper DDL commands are not executed – at times, the SQL/T-SQL/PL/SQL code is not even saved into source code control and the DB schema (and backup and restore) is used in order to do source code control for the DDL code. More on that in another post. In this one, we will look into how to create named default constraints rather than system generated ones. And if you have a schema with system generated default constraints, how can you go about finding those constraints in case you need to remove them or drop the columns that they are based on.

The proper way of doing this is:

CREATE TABLE CRAZYFROG (CRAZYFROG_ID INT IDENTITY, FROGNAME NVARCHAR(50));


--Add the column
ALTER TABLE CRAZYFROG ADD LEAP INT
GO
--add the default constraint to the table and assign it to the column
ALTER TABLE CRAZYFROG ADD
CONSTRAINT DF_CRAZYFROG_LEAP DEFAULT (5) FOR LEAP
GO
--Make the column not null.
ALTER TABLE CRAZYFROG ALTER COLUMN LEAP INT NOT NULL
GO

--NOW WHACK IT
ALTER TABLE CRAZYFROG DROP CONSTRAINT DF_CRAZYFROG_LEAP
GO
ALTER TABLE CRAZYFROG DROP COLUMN LEAP
GO


And here is the system generated way of doing it:

--*******************************************************************************
--THE LAZY MAN'S WAY. Later on you will have to look up the constraint name
--*******************************************************************************
--Add Column
ALTER TABLE crazyfrog ADD leap VARCHAR(20) default 10
GO


--Drop Column
ALTER TABLE crazyfrog DROP CONSTRAINT DF__crazyfrog__leap__259D3B88
GO
ALTER TABLE crazyfrog DROP COLUMN leap
GO

In order to look up the default constraint name, you can either do:

sp_helpconstraint crazyfrog

or you can extract it from sys.default_constraints – if we want to look up all the default constraints for the table, we can simply do this:

select * from sys.default_constraints where object_name(parent_object_id) = ‘crazyfrog’

The first method is of course a better one and less cumbersome. If you follow a particular nomenclature and adhere to it, you do not have to look up anything. In our projects, we always use a data modeling tool like ERWin or Sybase’s PowerDesigner and configure it such that the DDL’s are actually generated from that tool and are also put into source code control. That also helps in multiple ways besides being the best practice – if the vendor decides to support Oracle or DB2 LUW or some other RDBMS down the line, one can use the tool to auto-generate the DDL for the schema without much effort rather than reverse engineering the schema and then using tools to convert it.

Resources:

  • sys.default_constraints – BOL Definition here.
  • MSDN SQL Tips post – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: