Default Constraint – SQL Server

The DEFAULT constraint is used to provide a column’s default value. If no alternative value is supplied, that is, if the user leaves the column blank, the default value will be appended to all new records.

DEFAULT is defined as a column attribute in the ANSI SQL standard, however, Microsoft implemented DEFAULT as a type of constraint. As a result, the ALTER TABLE…ALTER COLUMN syntax cannot be used to alter a DEFAULT. Instead, remove the DEFAULT constraint as you would any other constraint object (for example, FOREIGN KEYS, PRIMARY KEYS, etc.) and re-add the DEFAULT constraint with the updated default value.

Example:
DROP TABLE IF EXISTS tbOrders
CREATE TABLE tbOrders
(
iOrderID INT IDENTITY(1,1) PRIMARY KEY,
dtOrderDate DATETIME DEFAULT(GETDATE()),
iOrderQty INT NOT NULL CHECK (iOrderQty>0),
iCustomerID INT,
vcOrderStatus VARCHAR(20) DEFAULT ‘Submitted’,
dtEstDeliveryDate DATETIME DEFAULT(GETDATE()+7),
vcOrderNotes VARCHAR(MAX))

The first column in the above table creation statement is an identification field that does not require any specified value. If the user leaves the dtOrderDate column blank, the system will insert the current date as its value. If no value is supplied for vcOrderStatus, the default value ‘Submitted’ is added. Unless otherwise specified, the approximate delivery date is 7 days from the order date.

The columns for which the default constraint is provided do not need to be specified in the INSERT statements. SQL Server will take care of things because we directed it to use the default settings if they are not explicitly provided.

INSERT INTO tbOrders(iOrderQty, iCustomerID, vcOrderNotes) VALUES (10,299219,’Priority Shipment’);
INSERT INTO tbOrders(iOrderQty, iCustomerID, vcOrderNotes) VALUES (20,299210,’Priority Shipment’);

SELECT * FROM tbOrders

CHECK Constraint Example