October 15, 2018 at 5:11 pm
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017
Why does constraint appear twice on the two line below in yellow in the columndefinition? One is capitalized, the other is not so I'm not sure why.
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATEcollation_name ]
[ SPARSE ]
[ MASKED WITH (FUNCTION = ' mask_function ') ]
[ CONSTRAINT constraint_name [DEFAULT constant_expression ] ]
[ IDENTITY [ (seed,increment ) ]
[ NOT FOR REPLICATION]
[ GENERATED ALWAYS ASROW { START | END } [ HIDDEN ] ]
[ NULL | NOT NULL]
[ ROWGUIDCOL ]
[ ENCRYPTEDWITH
(COLUMN_ENCRYPTION_KEY = key_name ,
ENCRYPTION_TYPE= { DETERMINISTIC | RANDOMIZED } ,
ALGORITHM= 'AEAD_AES_256_CBC_HMAC_SHA_256'
)]
[ <column_constraint> [, ...n ]]
[ <column_index>]
October 16, 2018 at 5:44 am
If you'll notice it appears a third time right next to the first CONSTRAINT. What you're seeing are different pieces of the command. The first, capitalized, reference is to the command itself. If you look at the examples, you can see it in use:
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)
It's just the command for creating the constraint. The second isn't referencing just the constraint, it's defining the constraint_name, FL_SpecialOfferProduct_SalesOrderDetail in the example. Then the third that you show is the column_constraint, which is the column, or columns, on which a constraint may be applied, different example needed:
CONSTRAINT CK_emp_id CHECKCHECK (emp_id (emp_id LIKELIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OROR emp_id emp_id LIKELIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
That column is being constrained by the constraint definition.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2018 at 5:49 am
Grant Fritchey - Tuesday, October 16, 2018 5:44 AMIf you'll notice it appears a third time right next to the first CONSTRAINT. What you're seeing are different pieces of the command. The first, capitalized, reference is to the command itself. If you look at the examples, you can see it in use:
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)It's just the command for creating the constraint. The second isn't referencing just the constraint, it's defining the constraint_name, FL_SpecialOfferProduct_SalesOrderDetail in the example. Then the third that you show is the column_constraint, which is the column, or columns, on which a constraint may be applied, different example needed:
CONSTRAINT CK_emp_id CHECKCHECK (emp_id (emp_id LIKELIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OROR emp_id emp_id LIKELIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')That column is being constrained by the constraint definition.
In think your second piece of code got discombobulated by the SSC paste interpreter Grant. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2018 at 6:24 am
Thom A - Tuesday, October 16, 2018 5:49 AMIn think your second piece of code got discombobulated by the SSC paste interpreter Grant. 🙁
Yeah, maybe. It's just a copy & paste from the linked article. Go there, scroll down to the samples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2018 at 6:31 am
The first is the syntax for a default constraint (enforce a default value for a column). The second is referencing another block of syntax for creating check, unique and foreign key constraints as part of the column definition.
Further down on the page you'll find where <column_constraint> is defined, it'll start as
<column_constraint> :: =
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2018 at 4:21 pm
GilaMonster - Tuesday, October 16, 2018 6:31 AMThe first is the syntax for a default constraint (enforce a default value for a column). The second is referencing another block of syntax for creating check, unique and foreign key constraints as part of the column definition.Further down on the page you'll find where <column_constraint> is defined, it'll start as
<column_constraint> :: =
I see what you mean. Looks like you can have more than one constraint in place for any given column, i.e. DEFAULT, NOT NULL, CHECK...….
October 16, 2018 at 4:24 pm
Grant Fritchey - Tuesday, October 16, 2018 5:44 AMIf you'll notice it appears a third time right next to the first CONSTRAINT. What you're seeing are different pieces of the command. The first, capitalized, reference is to the command itself. If you look at the examples, you can see it in use:
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)It's just the command for creating the constraint. The second isn't referencing just the constraint, it's defining the constraint_name, FL_SpecialOfferProduct_SalesOrderDetail in the example. Then the third that you show is the column_constraint, which is the column, or columns, on which a constraint may be applied, different example needed:
CONSTRAINT CK_emp_id CHECKCHECK (emp_id (emp_id LIKELIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OROR emp_id emp_id LIKELIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')That column is being constrained by the constraint definition.
Thank you everybody. I am still trying to get used to reading all the syntax for all these definitions. Seems so confusing and overwhelming, but as I ask more questions and read the answers, it is getting easier to understand. Thank you again.
October 16, 2018 at 5:05 pm
michael.leach2015 - Tuesday, October 16, 2018 4:24 PMThank you everybody. I am still trying to get used to reading all the syntax for all these definitions. Seems so confusing and overwhelming, but as I ask more questions and read the answers, it is getting easier to understand. Thank you again.
One issue is that the <column_constraint> section currently indicates there should be a comma between column constraints, but that is syntactically incorrect - in fact introducing a comma starts another column which sort-of looks like it works for constraints like CHECK which have identical syntax when used as Table Constraints, but breaks for things like Foreign Key constraints (which have subtly different syntax). This is an incorrect attempt to fix the documentation around multiple CHECK constraints which are valid, but additional ones have to be specified as table constraints - this is one of the particularly clunky areas of SQL syntax.
I should probably file a bug against this if I could figure out where the best place is these days....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply