A DEFAULT constraint supplies a value when an INSERT omits the column. I test omitted values separately from explicit NULL.
Developers ask me for a small default-constraint example. My earlier Customers.Address2 example used UNKNOWN but omitted the ADD keyword. The corrected script keeps that same column lesson and value.
CREATE TABLE #Customers (CustomerID int PRIMARY KEY, Address2 varchar(30) NULL);
INSERT INTO #Customers VALUES (1,'Existing'),(2,NULL);
ALTER TABLE #Customers ADD CONSTRAINT DF_Example_Address2 DEFAULT ('UNKNOWN') FOR Address2;
INSERT INTO #Customers (CustomerID) VALUES (3);
INSERT INTO #Customers (CustomerID, Address2) VALUES (4,NULL);
SELECT CustomerID, Address2 FROM #Customers ORDER BY CustomerID;
DROP TABLE #Customers;The existing value stays Existing, and the existing NULL stays NULL. The omitted Address2 for customer 3 receives UNKNOWN. Customer 4 explicitly inserts NULL, so the nullable column keeps NULL.
Choose a default compatible with the column type. A NOT NULL column rejects an explicit NULL even when it has a default. Existing data and an already-defined default require review before changing a real table.
The temporary table keeps the example self-contained. It demonstrates the default without changing a customer table.
Related reading
- 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?
A default is not a replacement for every missing value, it is a rule for omitted column input.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.