SQL SERVER – Add Column With Default Column Constraint to Table

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)

67 thoughts on “SQL SERVER – Add Column With Default Column Constraint to Table

  1. 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.

    Like

  2. 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

    Like

  3. @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.

    Like

  4. 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

    Like

  5. Santhosh;

    I suggest you download Microsofts free database software ‘Sql Server Express, and ‘SQL Server Manager Express’ and then search for ‘Beginning, SQL Server’ under Google videos. Microsofts got some good beginner videos as well. … I started with reading a few books on SQL, and use ‘SQL Authority’ as my key source for help on SQL Server queries etc.

    good luck

    Like

  6. Server Error in ‘/’ Application.
    ——————————————————————————–

    Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

    Source Error:

    Line 40: cmd.CommandType = System.Data.CommandType.Text;
    Line 41: String s = String.Empty;
    Line 42: s = String.Format(“insert into Customerdetails2 values(‘{0}’,'{1}’,{2},{3},{4},{5},'{6}’,'{7}’,'{8}’,'{9}’,'{11}’)”,
    Line 43: TextBox1.Text, TextBox2.Text, DropDownList1.SelectedItem.ToString(), DropDownList2.SelectedItem.ToString(), TextBox5.Text, TextBox6.Text,
    Line 44: TextBox7.Text, TextBox8.Text, TextBox9.Text, TextBox10.Text,TextBox11.Text);

    Source File: E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs Line: 42

    Stack Trace:

    [FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.]
    System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) +7485085
    System.String.Format(IFormatProvider provider, String format, Object[] args) +107
    System.String.Format(String format, Object[] args) +9
    cusdetails._Default.Button1_Click(Object sender, EventArgs e) in E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs:42
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

    ——————————————————————————–
    Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4927

    Like

  7. The information was quite helpful.Thank you
    I have a table with 5 columns and i need to insert my new column after the second column.How do i achieve this.

    Like

  8. Hi,
    I’m using SQL Server 2008. I’m dynamically creating a Table with default value of ‘NP’ for a some columns. that columns may have “Null” string value, so I would like to set the default value(“NP”) for “Null” string while creating a Table. Please help me on this. If this not possible,please let me know.

    Thanks
    Alexis

    Like

  9. This constraint doesnot add the default value in existing rows in the table??…

    ALTER TABLE dbo.utl_site ADD AMI_active CHAR(1)
    CONSTRAINT DF_utl_site_AMI_active DEFAULT ‘N’ NULL

    all of the rows of utl_site still remains NULL not ‘N’

    Can u explain please?

    Like

  10. Hello Sir,

    I have a requirement where in I want to add a Column which can calculate values by its own.
    What I want to do is I want to add a expression to that column.
    I could do that in visual studio using Datatable.columns(index).expression. but it consumes lot of time. please help.

    Here’s a small scenario
    CREATE TABLE #Temp (RowNo int identity(1,1),Quantity Decimal(18,5) ,ID int, StudentID int)

    Insert into #Temp
    Select Quantity,ID, StudentID From Student Where ID = 53

    Select * from #Temp

    Alter Table #Temp Add Calc Decimal(18,5) Default (‘[Quantity] * [ID] * 100′)

    Select * from #Temp
    Drop Table #Temp

    Like

  11. Thanks for your valuable time and quick reply.

    There are more than 13000 rows for my condition.
    Since I already had computed columns in the table and I want to use the same columns for my new Computed column, I put it in code.
    But because of the performance issue I need to do that in the back end itself.

    Like

  12. i want to add a coloumn name in my table name member aster but it allow only null value otherwise it say it’s not possible try with alter table plz help me
    the given error by sql 2005 is :-
    ‘MemberMaster’ table
    – Unable to modify table.
    ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘MemberLogin’ cannot be added to non-empty table ‘MemberMaster’ because it does not satisfy these conditions.

    help help help

    Like

  13. hi everyone
    can you help me how to set grid column
    my gode is here
    Dim sql As String = “select code,Name,curr as [Currency],LOCShtNM as [Short Name] from cost_center order By code”
    Dim cmd As New SqlCommand(sql, conn)
    dset.Clear()
    Dim adapt As New SqlDataAdapter
    adapt.SelectCommand = cmd
    adapt.Fill(dset, “cost_center”)
    DataGridView1.DataSource = dset
    DataGridView1.DataMember = “cost_center”
    conn.Close()
    DataGridView1.Columns(2).Width = 280

    Like

  14. Hi Dave,

    I have a query where I need to create a column say Status.
    It is a Not-Null column. The table I m gonna add this column, already have some data in it.

    So, wen creating the column I gave a Default value as 1.

    Now after addition of this column, in the same script file, I dropped the default constraint. It throws like ‘Status’ column not found.

    Please help me out

    Like

  15. I want to set a column with a default value = value of primary key column. How can I do this? I cannot set col2 value as computed column because I may want to override its value later but by default it should be = col1 value of the same table which is a primary key column. Your help will be appreciated.
    Thanks!

    Like

  16. –To add a column to display date and time
    ALTER TABLE [tablename]
    ADD [newcol] DATETIME not null default getdate()

    –To display the most recent insert
    SELECT * FROM [tablename]
    WHERE [newcol] =
    (
    SELECT MAX([newcol])
    FROM [tablename]
    )

    Like

  17. create table od
    (
    id int not null,
    name char(10),
    place char(30) DEFAULT ‘New_Delhi’,
    dat dateTIME DEFAULT GETDATE()
    )
    insert into od values(83,’AMIT’,”,”)
    select * from od

    but
    it show’s result
    id name place dat
    ———– ———- —————————— ———————–
    83 kk 1900-01-01 00:00:00.000

    wrong Date and place is Empty, please solve my problem

    Like

    • If you enter ” for place and date, this means you are inserting blanks, therefore it will not take default values.

      Use the following query:

      insert into od (id, name) values (83,’AMIT’)

      id name place dat
      83 AMIT New_Delhi 2013-03-04 09:54:52.047

      Like

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

  19. I want to check the default constraint is exist or not , If not I will add else ignore
    I have query like below but if I run multiple times it gives me error.

    if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘orderhdr’ AND COLUMN_NAME = ‘stphone’ AND COLUMN_DEFAULT IS NOT NULL))
    begin
    ALTER TABLE [dbo].[orderhdr] ADD DEFAULT ((‘ ‘)) FOR stphone
    End

    Regards,
    Chakradhar.

    Like

  20. Hi,
    I want to check the default constraint exist or not , Have below query it’s giving me error if I run multiple times.

    if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘orderhdr’ AND COLUMN_NAME = ‘stphone’ AND COLUMN_DEFAULT IS NOT NULL))
    begin
    ALTER TABLE [dbo].[orderhdr] ADD DEFAULT ((‘ ‘)) FOR stphone
    End

    Regards,
    Chakradhar.

    Like

  21. Hi, I have a question. If you add a column A TEXT(8) with a default value ‘ ‘, when you insert a row next time, this column A is set to ‘ ‘, while the problem is that the existing rows will not be set to ‘ ‘.

    This problem happens in MDB, when I ad a column to a table with default ‘ ‘, the new added column’s value is null, not the default value ‘ ‘, only when I insert a new row, then the column will be set to ‘ ‘

    Like

  22. Hi i have a scenario…will you please ans it ?

    i have a flat file as source having 10000 rows. i have to move it to destination as csv file. and i need to skip some top most rows and bottom rows some % ( varying ). will you please solve this?

    hope you got it :)

    Thanks in advance.

    Like

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