October 5, 2018 at 7:32 pm
In the documentation for CREATE TABLE (Transact-SQL), under check constraints, I see:
A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order they are created.
And in the syntax section:
<column_definition> ::= column_name <data_type>
...
[ <column_constraint> [ ...n ] ]
which supports the statement. However, when I try this with a toy example:
create table # (a int check (a < 42) check (a > 42))
I get the error message:
Msg 8148, Level 16, State 0, Line 4
More than one column CHECK constraint specified for column 'a', table '#'.
What am I doing wrong? How do I resolve this apparent inconsistency? Is it just me or is the documentation wrong about this?
(Note, I can add the second constraint later with an ALTER statement. That works fine.)
Gerald Britton, Pluralsight courses
October 5, 2018 at 7:44 pm
Found the problem. You need a comma between each check constraint. The doc seems to be missing that info.
Gerald Britton, Pluralsight courses
October 6, 2018 at 2:28 am
Quick thought, better use named constraint, otherwise it will be much harder to debug any code or data that conflicts with the constraint
😎
I will reject any code that does not conform to the constraint naming rules😉
Example of well formed table DDL:
USE TEEST;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.TBL_TEST_CONSTRAINT;
CREATE TABLE dbo.TBL_TEST_CONSTRAINT
(
TTC_ID INT IDENTITY(2,2) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_CONSTRAINT_TTC_ID PRIMARY KEY CLUSTERED
,CONSTRAINT CHCK_DBO_TBL_TEST_CONSTRAINT_TTC_ID_GT_ONE CHECK (TTC_ID > 1)
,CONSTRAINT CHCK_DBO_TBL_TEST_CONSTRAINT_TTC_ID_EVEN_NO CHECK ((TTC_ID % 2) = 0)
,TTC_TXT VARCHAR(15) NOT NULL CONSTRAINT CHCK_DBO_TBL_TEST_CONSTRAINT_TTC_MIN_LEN CHECK (LEN(TTC_TXT) > 5)
,CONSTRAINT CHCK_DBO_TBL_TEST_CONSTRAINT_TTC_NONUM CHECK (ISNUMERIC(TTC_TXT) = 0)
);
October 6, 2018 at 7:23 am
Quick thought, better use named constraint, otherwise it will be much harder to debug any code or data that conflicts with the constraint
Well, yes, of course, but that's not the point here. FWIW I always post questions as minimally as possible to expose the issue, bug, feature or whatever it is. (I did say "toy example"!)
Gerald Britton, Pluralsight courses
October 6, 2018 at 7:29 am
I think we have bugs both in the doc and in the engine. The doc bug is obvious: You need a comma between check constraints, if you have more than one on a column. The engine bug? When you omit the comma, the error message says that
More than one column CHECK constraint specified for column 'a', table '#'.
Which is true, of course, but not an error! The doc is correct here:
A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order they are created.
but the error message appears to contradict this only because it is choking on the bad syntax.
Gerald Britton, Pluralsight courses
October 7, 2018 at 6:00 am
g.britton - Saturday, October 6, 2018 7:23 AMQuick thought, better use named constraint, otherwise it will be much harder to debug any code or data that conflicts with the constraint
Well, yes, of course, but that's not the point here. FWIW I always post questions as minimally as possible to expose the issue, bug, feature or whatever it is.
My point was to provide an example of properly named constraints for anyone that picks up this thread.
😎
If you look at the documentation, the comma separation is there (bold), applies for any repeatable entries 😉
--Disk-Based
CREATE TABLE Syntax CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ]
( { <column_definition> | <computed_column_definition>
| <column_set_definition>
| [ <table_constraint> ]
| [ <table_index> ] }
[ ,...n ]
[ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name
, system_end_time_column_name ) ]
October 7, 2018 at 1:05 pm
My point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
Gerald Britton, Pluralsight courses
October 7, 2018 at 8:39 pm
g.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
And if you look at almost any thread on ssc you will find that people point out things like this all the time, and that they even go off on totally unrelated tangents.
October 8, 2018 at 7:43 am
Lynn Pettis - Sunday, October 7, 2018 8:39 PMg.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
And if you look at almost any thread on ssc you will find that people point out things like this all the time, and that they even go off on totally unrelated tangents.
Yeah Lynn I know! Sometimes those tangential discussions are pretty interesting. Still as the OP here, I feel I have the right, if not an obligation, to call out hijacking of my own thread!
Gerald Britton, Pluralsight courses
October 8, 2018 at 7:58 am
g.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
I have not hi-jacked the thread, I've only demonstrated two things, your fault in reading the documentation (no bug there) and a good / best practices in implementing multiple constraints on a column.
😎
Your OP was inaccurate, you stated that the syntax section was [ <column_constraint> [ ...n ] ] when it actually is [ <column_constraint> [,...n ] ] , missing that comma is the whole cause of your syntax problem.
October 8, 2018 at 8:09 am
Eirikur Eiriksson - Monday, October 8, 2018 7:58 AMg.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
I have not hi-jacked the thread, I've only demonstrated two things, your fault in reading the documentation (no bug there) and a good / best practices in implementing multiple constraints on a column.
😎Your OP was inaccurate, you stated that the syntax section was [ <column_constraint> [ ...n ] ] when it actually is [ <column_constraint> [,...n ] ] , missing that comma is the whole cause of your syntax problem.
My OP is accurate. The problem still exists. The documentation is incorrect at the moment. In fact I just submitted a pull request to get it fixed. Since the pull request has not been processed the page is still inaccurate as I write this (just search for it):
[ <column_constraint> [ ...n ] ]
Also there is a clear bug in the engine's parser/lexer, as it issues a nonsensical error message under this circumstance.
There is, in fact, no part of that page that includes a comma in the list. There will be when my pull request is merged though! 🙂
In case you're interested: Pull request
Also, since this is not a thread about best practices, then yes, you have hijacked it
Gerald Britton, Pluralsight courses
October 8, 2018 at 8:32 am
g.britton - Monday, October 8, 2018 8:09 AMEirikur Eiriksson - Monday, October 8, 2018 7:58 AMg.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
I have not hi-jacked the thread, I've only demonstrated two things, your fault in reading the documentation (no bug there) and a good / best practices in implementing multiple constraints on a column.
😎Your OP was inaccurate, you stated that the syntax section was [ <column_constraint> [ ...n ] ] when it actually is [ <column_constraint> [,...n ] ] , missing that comma is the whole cause of your syntax problem.
My OP is accurate. The problem still exists. The documentation is incorrect at the moment. In fact I just submitted a pull request to get it fixed. Since the pull request has not been processed the page is still inaccurate as I write this (just search for it):
[ <column_constraint> [ ...n ] ]
Also there is a clear bug in the engine's parser/lexer, as it issues a nonsensical error message under this circumstance.
There is, in fact, no part of that page that includes a comma in the list. There will be when my pull request is merged though! 🙂
In case you're interested: Pull request
Also, since this is not a thread about best practices, then yes, you have hijacked it
Sorry, and I stand corrected, there is a discrepancy in the documentation for SQL Server 2017.
😎
Having answered the question What am I doing wrong? How do I resolve this apparent inconsistency? Is it just me or is the documentation wrong about this? with a correct and best practice answer, I am wondering on what is the issue here?
As Lynn pointed out, on SSC and within the SSC, there is an ongoing accumulated effort of providing the fullest and the most accurate answers to any question, we are not doing this to promote our self's in any way shape or form. The thought is to provide the best answers possible and everyone that can improve the answer will pitch in.
October 8, 2018 at 8:40 am
Eirikur Eiriksson - Monday, October 8, 2018 7:58 AMg.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
I have not hi-jacked the thread, I've only demonstrated two things, your fault in reading the documentation (no bug there) and a good / best practices in implementing multiple constraints on a column.
😎Your OP was inaccurate, you stated that the syntax section was [ <column_constraint> [ ...n ] ] when it actually is [ <column_constraint> [,...n ] ] , missing that comma is the whole cause of your syntax problem.
It is all in the details my friend.. today you forget a comma and tomorrow you start accusing folks lending a helping hand of hi-jacking a thread in order to help get the record straight.
g.britton it's awesome that you contribute we are all here to learn and help others along the way. Accuracy however is vital to the content of this most amazing community.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 8, 2018 at 10:42 am
g.britton - Friday, October 5, 2018 7:32 PMIn the documentation for CREATE TABLE (Transact-SQL), under check constraints, I see:A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order they are created.
And in the syntax section:
<column_definition> ::= column_name <data_type>
...
[ <column_constraint> [ ...n ] ]which supports the statement. However, when I try this with a toy example:
create table # (a int check (a < 42) check (a > 42))I get the error message:
Msg 8148, Level 16, State 0, Line 4
More than one column CHECK constraint specified for column 'a', table '#'.What am I doing wrong? How do I resolve this apparent inconsistency? Is it just me or is the documentation wrong about this?
(Note, I can add the second constraint later with an ALTER statement. That works fine.)
This works:create table #a (a int check ((a < 42) or (a > 42)))
The syntax from the BOL for CREATE TABLE doesn't indicate you can put more than one check constraint on a column with the create table statement:
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
[ AS FileTable ]
( { <column_definition>
| <computed_column_definition>
| <column_set_definition>
| [ <table_constraint> ]
| [ <table_index> ] }
[ ,...n ]
[ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name
, system_end_time_column_name ) ]
)
[ ON { partition_scheme_name ( partition_column_name )
| filegroup
| "default" } ]
[ TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name
| filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ SPARSE ]
[ MASKED WITH ( FUNCTION = ' mask_function ') ]
[ CONSTRAINT constraint_name [ DEFAULT constant_expression ] ]
[ IDENTITY [ ( seed,increment ) ]
[ NOT FOR REPLICATION ]
[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]
[ NULL | NOT NULL ]
[ ROWGUIDCOL ]
[ ENCRYPTED WITH
( COLUMN_ENCRYPTION_KEY = key_name ,
ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED } ,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) ]
[ <column_constraint> [ ...n ] ]
[ <column_index> ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
October 8, 2018 at 10:59 am
g.britton - Monday, October 8, 2018 7:43 AMLynn Pettis - Sunday, October 7, 2018 8:39 PMg.britton - Sunday, October 7, 2018 1:05 PMMy point was to provide an example of properly named constraints for anyone that picks up this thread.
I get that. However what has happened is that you have successfully hi-jacked the thread to make it about something else. Not that I disagree with your point. Like you, I enforce these things in code reviews and teach them in lessons.
Now, my fear is that someone who picks up the thread will start at the end and think its about best practices (which it now is) and completely miss the bugs I reported. OTOH if they start at the start, they will immediately see that I posted a toy example, (and called it that) and wonder how the discussion veered off to be about best practices.
And if you look at almost any thread on ssc you will find that people point out things like this all the time, and that they even go off on totally unrelated tangents.
Yeah Lynn I know! Sometimes those tangential discussions are pretty interesting. Still as the OP here, I feel I have the right, if not an obligation, to call out hijacking of my own thread!
Actually, not. You aren't paying for advice and neither are we getting paid for giving it. If you don't like it you could always as for your money back.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply