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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE Pantheon; | |
| GO | |
| DROP TABLE IF EXISTS dbo.CreateThenAlter; | |
| CREATE TABLE dbo.CreateThenAlter | |
| ( | |
| RBAR char(10) | |
| ); | |
| GO | |
| 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 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE IF EXISTS dbo.AllInLine; | |
| CREATE TABLE dbo.AllInLine | |
| ( | |
| CheckyMcCheckFace char(10) | |
| 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.