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.
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.
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.
View All Default Constraints On a Table
Using following query, we can fetch the list of default constraints created on a table.
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.
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.
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.