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.
|DROP TABLE IF EXISTS dbo.CreateThenAlter;|
|CREATE TABLE dbo.CreateThenAlter|
|ALTER TABLE dbo.CreateThenAlter ADD CONSTRAINT CHK_CreateThenAlter_RBAR CHECK (RBAR IN ('good', 'bad'));|
|ALTER TABLE dbo.CreateThenAlter ADD CONSTRAINT DF_CreateThenAlter_RBAR DEFAULT ('good') FOR RBAR;|
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!
|DROP TABLE IF EXISTS dbo.AllInLine;|
|CREATE TABLE dbo.AllInLine|
|CONSTRAINT CHK_AllInLine_CheckyMcCheckFace CHECK (CheckyMcCheckFace IN ('good', 'bad'))|
|CONSTRAINT DF_AllInLine_CheckyMcCheckFace DEFAULT ('good')|
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.