Multiple Inline Constraints

SQL New Blogger:

Time to investigate: 10 mins 
Time to test: 10 mins
Time to write: 10 mins

While creating a script for some new tables I came across a few columns that were designated to have both CHECK constraints and DEFAULT constraints.

Now this isn’t a problem of itself, it can be easily achieved by using a CREATE TABLE statement and then using 2 ALTER TABLE statements to create the constraints.

Old Style:

The problem that I had with this was that, so far, I was going along and creating these tables & columns with the constraints created in-line and it just galled me to have to break this flow and create these constraints as ALTER statements.

Checking the examples in the new Microsoft Docs didn’t show any examples that I could find of creating both constraints together on the same column so I experimented and found out that you can!

Here’s how…

New Style:

Notice 2 things here:

  1. There is no need to specify a FOR <column name> on the default constraint because SQL Server can tell the constraint is to work on the column it is currently defining.
  2. There is no comma separating the two constraints. This would break the inline property of these statements and SQL Server would think you’ve messed up syntax on a constraint (this got me for a sec).

Great, I can keep my constraints inline!

That’s a wrap

Documentation is useful but they do not cover every situation. Have a test environment; Hypothesize, test, and verify. You never know what you’d find.