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
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:
- SQL SERVER – Add Column With Default Column Constraint to Table
- SQL SERVER – Add New Column With Default Value
- How Default Value and Nullable Column Works? – Interview Question of the Week #129
- SQL SERVER – How to Disable and Enable All Constraint for Table and Database
- SQL SERVER – What is is_not_trusted in sys.foreign_keys?
Let me know your opinion about this subject in the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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
Thank you very much. I´ve just begun to develop things and this script was very useful for me.
2 Karina
use CHECK sample below:
ALTER TABLE
ADD CONSTRAINT CHECK ( IN (‘N’,’Y’));
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
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.
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
ALTER TABLE [dbo].[table_name] ADD DEFAULT (0) FOR [column_name]
I have done this but now upon insert of a new record the value is not defaulting to 0 (zero) why?
Thank youi, it was just what I’ve needed.
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
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.
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”
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,
You forgot the “ADD” keyword
You are right.