Constraints in SQL database maintains data integrity and accuracy. SQL Default Constraint is used to assign a default value to a column when no value is specified. Using Default constraint we can have a meaningful value instead of storing NULL or Empty value. It reduces the presence of NULLs when applicable by improving data consistency.
A column having some meaningful data using default constraint is greatly beneficial rather than having no data. For an example, when an employee joins a new organization – his status, by default set to Active. Thus each time, instead of supplying Active status value while inserting new employee record. We can use Default constraint on a Status column with the default value set to Active.
Default constraint can be added while creating a new table or we may add to a column in an existing table using ALTER TABLE.
SQL Default Constraint – Points To Ponder
1.Used to specify a default value instead of NULL or unsuitable value using default constraint.
2.keyword default is used in SQL to specify a default value when no value is specified.
3.Use ALTER TABLE to add a default constraint to the existing table.
4.Cannot be added on TimeStamp and IDENTITY typed columns.
5.Can’t modify existing default constraint, we must drop and re-create default constraint every time if a requirement has changed.
6.Can specify string, number, date, or decimal value in default constraint to work with most SQL data types.
Creating Default Constraint with New Table
Generally, we can define, Default constraint while creating a new table. In addition to this, a good practice to always specify constraint name – otherwise SQL Server will assign a default name, which is considerably tough to read.
In this following example, we are creating an Employee table with added default constraint on Status and CreatedDate column. In default constraint definition you can see the default value Active for Status column and dynamic default value using GETDATE function for CreatedDate column.
Using the default keyword within an INSERT statement to use DEFAULT constraint values.
USE tempdb; GO IF OBJECT_ID('Employee','U') IS NOT NULL DROP TABLE Employee; CREATE TABLE Employee ( Id INT IDENTITY (1,1) ,Name VARCHAR(50) NOT NULL ,City VARCHAR(50) NOT NULL ,Status VARCHAR(10) NOT NULL CONSTRAINT DF_Emp_Status DEFAULT 'Active' ,CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_Emp_CreatedDate DEFAULT GETDATE() ); GO INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES ('Vishal', 'Mumbai', default, default) -- use default keyword to supply default constraint values ,('Sangram', 'Pune', default, default) ,('Atul', 'Mumbai', 'InActive', GETDATE()); --Over riding default constraint values SELECT * FROM Employee;
Creating Default Constraint On Existing Column
Likewise, we could have a requirement to add Default constraint on an existing column. In this example, the Employee table is already created. Supposedly, we need to add default constraint on City column. Considering most of our employees are from Mumbai city.
In the following query using ALTER TABLE and adding Default constraint.
Use tempdb; GO ALTER TABLE Employee ADD CONSTRAINT DF_Emp_City DEFAULT 'Mumbai' FOR City; GO INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES ('Shailesh', default, default, default) -- use default keyword to supply default constraint values SELECT * FROM Employee;
Overriding Default Constraint Values
In case you have relevant values for all the fields in a table. Then you can specify them within INSERT statement to override default constraint. In this example, we have specified values for all the columns having Default constraint. Therefore, default values will be overwritten.
INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES ('Chetan', 'Pune', 'Active', GETDATE()) -- use default keyword to supply default constraint values SELECT * FROM Employee;
View All Default Constraints On a Table
Using following query, we can fetch the list of default constraints created on a table.
--List all Default Constraint SELECT name AS DefaultConstraintName, OBJECT_NAME(parent_object_id) AS TableName, COL_NAME(parent_object_id, parent_column_id) AS ColumnName, type_desc AS Type, create_date AS CreatedDate, definition AS DefaultValue FROM sys.default_constraints --system view storing all default constraint info WHERE OBJECT_NAME(parent_object_id) = 'Employee';
Especially if you want to see all the constraints created on a table using a system created stored procedure then we can use sp_helpconstraint by passing the table name as input to fetch the list of constraints available.
--View All Constraint EXEC sp_helpconstraint 'Employee';
Drop Default Constraint
For instance, we have decided to not make Mumbai as default city value. As a result, we can drop default constraint defined on City column.
IF OBJECT_ID('DF_Emp_City','D') IS NOT NULL ALTER TABLE Employee DROP CONSTRAINT DF_Emp_City, COLUMN City GO
References
Beginners Guide to CHECK Constraints in SQL Server
How To Use Unique Constraint In SQL Server
Summary
Firstly, we have learn about uses of Default constraint in SQL Server. Addition to this, we have seen examples using Default constraint on columns to insert default value using keyword default instead of specifying any value. If you like this post you can visit Tech-Recipes Database archives.