SQL New Blogger: Time to investigate: 10 mins Time to test: 10 mins Time to write: 10 mins
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.
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
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!
Notice 2 things here:
- 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.
- 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.