SQL SERVER – Add Column With Default Column Constraint to Table « Journey to SQL Authority with Pinal Dave

SQL SERVER – Add Column With Default Column Constraint to Table

March 19, 2008 by pinaldave

Just a day ago while working with database Jr. Developer asked me question how to add column along with column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.

It is very easy to add column and specify default constraint. I have seen many examples where constraint name is not specified, if constraint name is not specified SQL Server will generate unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here : SQL SERVER Database Coding Standards and Guidelines Complete List Download

ALTER TABLE TestTable ADD NewCol VARCHAR(50) CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 8 Comments

8 Responses

on March 22, 2008 at 12:27 am | Reply Larry

Dave,

I think something is wrong with your blog software. The new blog post today is labeled as the 19th, but you posted it today, 3/21. Just wanted you to be aware.
on July 18, 2008 at 9:11 pm | Reply adiraju

hello sir,

i could not delete a column in a table through ‘ drop column’ option in ‘alter table’ query even though the column is not having any relationships or any other constraints.
please help me.

with regards,
adiraju
on July 18, 2008 at 10:07 pm | Reply Imran Mohammed

@adiraju

If that column you are trying to drop is used by any derived column, then you cannot drop it.

Example: in Example11 I created a table with out any derived column so I was able to drop column ename.

CREATE TABLE EXAMPLE11( EID INT , ENAME VARCHAR(10))
GO
ALTER TABLE EXAMPLE11
DROP COLUMN ENAME
GO

Result:Command(s) completed successfully.

Example2: In this example I created third column using first and second column, so when I try to drop second column, it gives me an error because second column in used in third column

CREATE TABLE EXAMPLE12 ( COLA INT, COLB INT , COLC AS COLA + COLB )
GO
ALTER TABLE EXAMPLE12
DROP COLUMN COLB
Go
Result: Msg 5074, Level 16, State 1, Line 1
The column ‘colc’ is dependent on column ‘COLB’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN COLB failed because one or more objects access this column.

Hope this helps.
Thanks,
Imran.
on October 8, 2008 at 8:40 am | Reply Treasa

Hi,

Can someone please tell me how to specify a default constraint for an existing column?

Thanks in advance!!

Treasa
on December 1, 2008 at 5:01 pm | Reply Musab

Dear Treasa!

ALTER TABLE TableName WITH NOCHECK
ADD CONSTRAINT DF_DefaultName DEFAULT ‘Default Value’ FOR ColumnName

Check it out !
on March 5, 2009 at 7:16 am | Reply Simon

I have an existing query which return 1000 of rows, i want to add another column and have constant numerical value (2.7 for example). can someone tell me how to do that? many thanks
on August 27, 2009 at 12:38 am | Reply Guilherme

até que enfim um exemplo simples e eficaz. boa=good!
on November 4, 2009 at 8:50 pm | Reply Eduardo

Thanks for the solution. Works!