AzureFeeds

Default constraints allow us to automatically provide values for columns when:

• we don’t supply them ourselves
• they are marked as NOT NULL so they are required

When you’re building an INSERT statement, you don’t have to give values for all columns. Columns that are declared as NULL aren’t mandatory, so you don’t have to supply those columns at all. Those columns can just be NULL.

But if you have a column that is declared as NOT NULL, so it requires a value, but you haven’t supplied it in the INSERT statement, then a default constraint can provide the value. This is configured as part of the table definition, or added as a constraint later by altering the table definition.

Naming Default Constraints

I always provide names for my default constraints, at least while I’m using SQL Server. If I don’t provide a name, then SQL Server will pick a name by itself, and they’re pretty horrible.

One common pattern is DF_SchemaName_TableName_ColumnName.

That works ok as you can only have a single default value for a column, so you won’t get naming clashes.

Notice that the example in the AdventureWorks database above ALMOST follows that pattern. If you had the same table name in two schemas, they’d have a problem with pattern they have used.

But why do I want a name in the first place?

First up, it makes comparing databases easier, particularly for CI/CD (Continuous Integration/Continuous Deployment) based systems.

Second, in SQL Server, you often need to refer to the defaults by name. For example, if I want to drop a column, SQL Server requires me to first drop any constraints on the column. And that’s a pain in the neck if you don’t know what the name is. So scripts for modifying databases end up far more complex than they should be.

Worth noting that this isn’t an issue in PostgreSQL. In that variant of SQL, you can’t give default constraints a name, but it doesn’t matter, because when you drop a column, it automatically drops associated constraints. I really wish SQL Server worked that way.

Special Values

When you are executing an INSERT statement, there are also two other special values to be aware of:

DEFAULT – this tells the database engine to load the default value defined for the column. You can use this if you need to specify the column in the INSERT but you want SQL Server to apply the column default instead of a supplied value.

DEFAULT VALUES – this one is rare, but it tells the database engine to use the default values for each column mentioned in the INSERT.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #130 What are Default Constraints in T-SQL? appeared first on The Bit Bucket.