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.

SQL SERVER - Create Default Constraint Over Table Column defaultconstraint

ALTER TABLE Customers
            CONSTRAINT DF_Customers_Address2
            DEFAULT 'UNKNOWN' FOR Address2

Remember currently I am using a default value as unknown as my column datatype is varchar. However, if the column is of datatype integer you have to specify the default value as an integer. Remember here we are specifying only default constraints so if the column already has a value the column will not be filled with the default values.

If the column is not nullable, it must either contain a value or will need a default constraint with a value, otherwise, the insert statement will fail. Here are few additional blog posts on a similar subject:

Let me know your opinion about this subject in the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Constraint and Keys, SQL Scripts, SQL Server
Previous Post
SQLAuthority News – 3 Million Readers and Continuing Journey
Next Post
SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 4

Related Posts

15 Comments. Leave new

  • 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

    Reply
  • Billigflug Phuket
    June 20, 2008 4:48 pm

    Thank you very much. I´ve just begun to develop things and this script was very useful for me.

    Reply
  • 2 Karina
    use CHECK sample below:
    ALTER TABLE
    ADD CONSTRAINT CHECK ( IN (‘N’,’Y’));

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Rahul Kumar Sharma
    September 6, 2011 4:30 pm

    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

    https://docs.microsoft.com/en-us/previous-versions/sql/compact/sql-server-compact-4.0/ms174123(v=sql.110)

    https://docs.microsoft.com/en-us/previous-versions/sql/compact/sql-server-compact-4.0/ms174123(v=sql.110)

    and
    the syntax where i getting error is

    ALTER TABLE BE_PromoCode
    ALTER COLUMN UsePromoNo SET DEFAULT 0

    Reply
  • ALTER TABLE [dbo].[table_name] ADD DEFAULT (0) FOR [column_name]

    Reply
  • Matt Fairfield
    January 10, 2013 2:16 am

    I have done this but now upon insert of a new record the value is not defaulting to 0 (zero) why?

    Reply
  • Thank youi, it was just what I’ve needed.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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”

    Reply
  • Sushil Agarwal
    December 6, 2015 8:10 pm

    Sir,
    How Can I Add a Column To a table and have it set as foreign key in a script ,
    i am trying
    ALTER TABLE [dbo].[bill] ADD mainsl_rowid int;
    ALTER TABLE [dbo].[bill] WITH CHECK ADD CONSTRAINT [FK_bill_mainsl] FOREIGN KEY(mainsl_rowid )
    REFERENCES [dbo].[mainsl] (rowid) ON DELETE NO ACTION ON UPDATE NO ACTION;
    update [dbo].[bill] set mainsl_rowid=b.rowid from [dbo].[bill] a join dbo.mainsl b on b.ui=a.mainsl_ui;

    but after add go is required , how can i do it without go,

    Reply
  • Diego Anckizes Silva
    March 18, 2017 4:27 am

    You forgot the “ADD” keyword

    Reply

Leave a Reply