SQL SERVER – Create Default Constraint Over Table Column

Very frequently Jr. Developers request script for creating default constraint over table column. I have written following small script for creating default constraint. I think this will be useful to many other developers who want this script to keep handy.

ALTER TABLE Customers
            ADD CONSTRAINT DF_Customers_Address2
            DEFAULT 'UNKNOWN' FOR Address2

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

About these ads

14 thoughts on “SQL SERVER – Create Default Constraint Over Table Column

  1. i have a problem like this:
    i have a column that can get the values: Y or N
    how do i wirte a script that allows only one row at every given moment to be Y and all others N.
    i want this as a constraint or something like this.
    thanks in advance,
    Karina

  2. Hi,

    is there a create script that can gather all the existing constraints on all tables in a db?

    I have foreign keys, primary keys, indexes and check constraints… but am unable to find one for default constraints.

    Thanks
    Riccardo

  3. I have a Domain Data table that is being added at the same time a FK is being created refrencing the new table. The problem is the data in the new table is not instered until the post script so the creation of the FK fails. How would you handle this? I tried createing the table in the pre script but then i fail because the table already exists when data dude tries to add it.

  4. hello sir

    i read this article and this is correct that we can add the default constraint by this , but on msdn and technet i found different syntax which is not working for me , giving me syntax error
    i am also posting the link where i found these information

    http://technet.microsoft.com/en-us/library/ms174123.aspx

    http://www.w3schools.com/sql/sql_default.asp

    http://msdn.microsoft.com/en-us/library/ms174123.aspx

    and
    the syntax where i getting error is

    ALTER TABLE BE_PromoCode
    ALTER COLUMN UsePromoNo SET DEFAULT 0

  5. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  7. i have tried same syntax for adding default contraint
    ALTER TABLE tblperosontable
    ADD CONSTRAINT DF_tblpersontable_genderid
    DEFAULT 3 for genderid
    but i get following error :
    “cannot find the object “tblperosontable” because it does not exist or you do not have permissions.”
    i have logged as SA user ..
    and can you please tell me how to add default constraint graphically in sql server management studio.
    Thanks for your help
    namita

  8. I need to set a column to NULL if an amount column in the same table is = 0, or set the column to NOT NULL if the amount column is > 0.

  9. Hi ,
    I am trying to create a constraint where I am trying to update a column to Y or N depending on two other fields in the table
    Ex : Create_Date and Expiry_date for a product is defined and another column named Active_Flag wherein if Create_Date is “2014-03-10 00:00:00.0000000″ and Expiry_Date is “2014-03-25 12:00:00.00000000″ and current_date “2014-03-19 00:00:00.0000000″ then automatically Active_Flag should update “Y”
    Ex2 : Create_Date and Expiry_date for a product is defined and another column named Active_Flag wherein if Create_Date is “2014-03-10 00:00:00.0000000″ and Expiry_Date is “2014-03-25 12:00:00.00000000″ and current_date “2014-03-27 00:00:00.0000000″ then automatically Active_Flag should update “N”

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