October 8, 2018 at 11:09 am
Jonathan AC Roberts - Monday, October 8, 2018 10:42 AMg.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 )
}
I think that's what the ellipsis in [ <column_constraint> [ ...n ] ] means
Gerald Britton, Pluralsight courses
October 8, 2018 at 11:09 am
Lynn Pettis - Monday, October 8, 2018 10:59 AMg.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.
LOL too true!
Gerald Britton, Pluralsight courses
October 8, 2018 at 11:10 am
Jeffery Williams - Monday, October 8, 2018 8:40 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.
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.
Thanks Jeffrey! Quite true, except that the doc is incorrect on this point. I submitted a PR to get it fixed.
Gerald Britton, Pluralsight courses
October 8, 2018 at 11:38 am
g.britton - Friday, October 5, 2018 7:44 PMFound the problem. You need a comma between each check constraint. The doc seems to be missing that info.
Good news! My pr was just merged!
https://github.com/MicrosoftDocs/sql-docs/pull/1049
Should appear on the page soon-ish
Gerald Britton, Pluralsight courses
October 8, 2018 at 1:14 pm
g.britton - Monday, October 8, 2018 11:09 AMI think that's what the ellipsis in [ <column_constraint> [ ...n ] ] means
Ah yes, thanks. So this works:create table #a (a int check (a < 42), check (a > 42))
But I don't think it allows any rows to be inserted into the table. It seems to be the equivalent of this:create table #a (a int check (a < 42) and (a > 42))
not this:create table #a (a int check (a < 42) or (a > 42))
October 10, 2018 at 6:05 am
drop table if exists test2
drop table if exists test1
create table test1
(
z int primary key,
y int not null
)
(
a int not null check (a< 10) primary key clustered foreign key references test1(z)
)
In fact if you do put commas in to separate them you get errors as they are assumed to be table level contraints with missing column lists.
October 10, 2018 at 6:45 am
andycadley - Wednesday, October 10, 2018 6:05 AMUnfortunately I'm not sure that's entirely correct either. The revised syntax suggests there should be a comma between every column constraint and yet that clearly isn't the case:
create table test2
drop table if exists test2
drop table if exists test1
create table test1
(
z int primary key,
y int not null
)
(
a int not null check (a< 10) primary key clustered foreign key references test1(z)
)In fact if you do put commas in to separate them you get errors as they are assumed to be table level contraints with missing column lists.
Simple syntax error
😎
-- Using named constraint
drop table if exists test2
drop table if exists test1
create table test1
(
z int primary key,
y int not null
)
create table test2
(
a int not null constraint pk_stuff primary key clustered
, constraint chck_stuff check (a< 10)
, constraint fk_stuff foreign key(a) references test1(z)
);
-- Using system generated names
drop table if exists test2
drop table if exists test1
create table test1
(
z int primary key,
y int not null
)
create table test2
(
a int not null primary key clustered
, check (a< 10)
, foreign key(a) references test1(z)
);
October 10, 2018 at 7:01 am
In both cases you're providing the foreign key constraint as a table constraint (hence having to specify the column "a") as opposed to specifying it as a column constraint (for which you don't need to).
October 12, 2018 at 7:18 am
g.britton - Friday, October 5, 2018 7:44 PMFound the problem. You need a comma between each check constraint. The doc seems to be missing that info.
Interesting anomaly. You can name constraints on tables except for table variables. e.g. this works:
declare @ table (a int check (a < 42), check (a > 42))
As does this:
create table # (a int constraint a1 check (a < 42), constraint a2 check (a > 42))
This doesn't
declare @ table (a int constraint a1 check (a < 42), constraint a2 check (a > 42))
Gerald Britton, Pluralsight courses
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply