Seeder question: I maintain a database which seems to use two different types of defaults - some columns have default constraints, and other columns have bound defaults. What is the difference, and is one preferable to the other?
asked
What is the difference between a default constraint and a bound default?
I think these do the same thing, make sure that if no data is in a field, that some default is placed there. For example, you might want a blank space instead of a null, or a default ship date of 30 days from the current date, if there is no data entered by the application.
It appears that the idea of a default bound to a column is likely to be removed and that a default constraint is the way to do this in the future.
A default constraint is a table attribute and requires the CREATE TABLE or ALTER TABLE syntax to manipulate.
A bound default is an SQL object external to a table that is... bound... to a column in a table. You use CREATE DEFAULT, sp_bindefault, sp_unbindefault and DROP DEFAULT to work with them. They are being discontinued in SQL.
They are both used to ensure a known value is populated into a column when a row is inserted into a table. If no value is specified for the column, or if the DEFAULT keyword is supplied, the constraint substitutes the value with it's pre-defined value. If a value is supplied during the insert for the column, the constraint allows it to be inserted without substitution.
Bound defaults can accidentally be dropped from a database and affect many tables unintentionally whereas dropping a default constraint affects only the one column, in the one table, where it was defined.
