|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Check constraint script problemI submit the following syntax for the check constraint but when it is in SQL it is changed and all my parantheses are gone except a couple. Weird. Please help. Syntax going in: CONSTRAINT CKFulfillmentPackage CHECK ((ResponsibleForCustomerContactFlg = 1 AND ( (AutomaticPackageGenerationRequestFlg = 0 AND FinancialApplicationContactBeginTs IS NOT NULL AND PackagePreparationTimelinessCd IS NULL ) OR (AutomaticPackageGenerationRequestFlg = 1 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL) ) ) OR (ResponsibleForCustomerContactFlg = 0 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL)) ) ----------------------------------NOT MISSING PARANTHESES AFTER THE LAST OR STMT------ --Syntax in SQL CHECK ([ResponsibleForCustomerContactFlg] = 1 and ([AutomaticPackageGenerationRequestFlg] = 0 and [FinancialApplicationContactBeginTs] is not null and [PackagePreparationTimelinessCd] is null or [AutomaticPackageGenerationRequestFlg] = 1 and [FinancialApplicationContactBeginTs] is null and [PackagePreparationTimelinessCd] is not null) or [ResponsibleForCustomerContactFlg] = 0 and [FinancialApplicationContactBeginTs] is null and [PackagePreparationTimelinessCd] is not null) ) SQLGuru_not wrote:
Show quote > I am running SQL 2000 SP4. Unlike stored procedures, things like constraints are not (I strongly> I submit the following syntax for the check constraint but when it is in SQL > it is changed and all my parantheses are gone except a couple. Weird. > Please help. > Syntax going in: > CONSTRAINT CKFulfillmentPackage > CHECK ((ResponsibleForCustomerContactFlg = 1 > AND > ( > (AutomaticPackageGenerationRequestFlg = 0 > AND FinancialApplicationContactBeginTs IS NOT NULL > AND PackagePreparationTimelinessCd IS NULL > ) > OR > (AutomaticPackageGenerationRequestFlg = 1 > AND FinancialApplicationContactBeginTs IS NULL > AND PackagePreparationTimelinessCd IS NOT NULL) > ) > ) > OR > (ResponsibleForCustomerContactFlg = 0 > AND FinancialApplicationContactBeginTs IS NULL > AND PackagePreparationTimelinessCd IS NOT NULL)) > ) > > ----------------------------------NOT MISSING PARANTHESES AFTER THE LAST OR > STMT------ > > --Syntax in SQL > CHECK ([ResponsibleForCustomerContactFlg] = 1 > and ([AutomaticPackageGenerationRequestFlg] = 0 > and [FinancialApplicationContactBeginTs] is not null > and [PackagePreparationTimelinessCd] is null > or [AutomaticPackageGenerationRequestFlg] = 1 > and [FinancialApplicationContactBeginTs] is null > and [PackagePreparationTimelinessCd] is not null) > or [ResponsibleForCustomerContactFlg] = 0 > and [FinancialApplicationContactBeginTs] is null > and [PackagePreparationTimelinessCd] is not null) > ) believe) stored in their textual form. I believe SQL Server has to create different constructs in order to allow it to process constraints quickly and efficiently. Therefore, when you ask DMO/SMO to produce a script for any of these objects, you'll get a translation back from the "compiled" form of the constraint, rather than what you initially entered. What you're getting back is equivalent to your initial statement. SQL doesn't need parantheses around the "ands" because, as BOL says: "When more than one logical operator is used in a statement, AND operators are evaluated first. You can change the order of evaluation by using parentheses." Damien Damien, thank you for this explanation but as the last part of the BOL
statement says you can change the order of operation by including parantheses. This is where the diconnect is. The parantheses which are intended to enforce a specific order of operation are removed by SQL. How can I get SQL to keep them? Show quote "Damien" wrote: > SQLGuru_not wrote: > > I am running SQL 2000 SP4. > > I submit the following syntax for the check constraint but when it is in SQL > > it is changed and all my parantheses are gone except a couple. Weird. > > Please help. > > Syntax going in: > > CONSTRAINT CKFulfillmentPackage > > CHECK ((ResponsibleForCustomerContactFlg = 1 > > AND > > ( > > (AutomaticPackageGenerationRequestFlg = 0 > > AND FinancialApplicationContactBeginTs IS NOT NULL > > AND PackagePreparationTimelinessCd IS NULL > > ) > > OR > > (AutomaticPackageGenerationRequestFlg = 1 > > AND FinancialApplicationContactBeginTs IS NULL > > AND PackagePreparationTimelinessCd IS NOT NULL) > > ) > > ) > > OR > > (ResponsibleForCustomerContactFlg = 0 > > AND FinancialApplicationContactBeginTs IS NULL > > AND PackagePreparationTimelinessCd IS NOT NULL)) > > ) > > > > ----------------------------------NOT MISSING PARANTHESES AFTER THE LAST OR > > STMT------ > > > > --Syntax in SQL > > CHECK ([ResponsibleForCustomerContactFlg] = 1 > > and ([AutomaticPackageGenerationRequestFlg] = 0 > > and [FinancialApplicationContactBeginTs] is not null > > and [PackagePreparationTimelinessCd] is null > > or [AutomaticPackageGenerationRequestFlg] = 1 > > and [FinancialApplicationContactBeginTs] is null > > and [PackagePreparationTimelinessCd] is not null) > > or [ResponsibleForCustomerContactFlg] = 0 > > and [FinancialApplicationContactBeginTs] is null > > and [PackagePreparationTimelinessCd] is not null) > > ) > > Unlike stored procedures, things like constraints are not (I strongly > believe) stored in their textual form. I believe SQL Server has to > create different constructs in order to allow it to process constraints > quickly and efficiently. Therefore, when you ask DMO/SMO to produce a > script for any of these objects, you'll get a translation back from the > "compiled" form of the constraint, rather than what you initially > entered. > > What you're getting back is equivalent to your initial statement. SQL > doesn't need parantheses around the "ands" because, as BOL says: > "When more than one logical operator is used in a statement, AND > operators are evaluated first. You can change the order of evaluation > by using parentheses." > > Damien > > You may be able to get around this using CASE. Try:
ALTER TABLE YourTable DROP CONSTRAINT CK_FulfillmentPackage GO ALTER TABLE YourTable ADD CONSTRAINT CK_FulfillmentPackage CHECK (CASE WHEN ResponsibleForCustomerContactFlg = 1 AND AutomaticPackageGenerationRequestFlg = 0 AND FinancialApplicationContactBeginTs IS NOT NULL AND PackagePreparationTimelinessCd IS NULL THEN 1 WHEN ResponsibleForCustomerContactFlg = 1 AND AutomaticPackageGenerationRequestFlg = 1 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL THEN 1 WHEN ResponsibleForCustomerContactFlg = 0 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL THEN 1 ELSE 0 END = 1) GO Damien's piont is that you are NOT changing the order of operations with
your parenthesis (at least not the ones removed by SQL Server). Although they make the code more explicit and easier for a person to understand, they are redundant to SQL Server. The parenthesis that it removed were around criteria connected by AND, which happens before OR, as if it had parenthesis around it. Your original code is doing exactly the same thing as the code without the extra parenthesis. The very same order of operations are enforced in both cases. Show quote "SQLGuru_not" <SQLGuru***@discussions.microsoft.com> wrote in message news:B1871A0A-5524-4B44-93F6-C343F08D221A@microsoft.com... > Damien, thank you for this explanation but as the last part of the BOL > statement says you can change the order of operation by including > parantheses. This is where the diconnect is. The parantheses which are > intended to enforce a specific order of operation are removed by SQL. How > can I get SQL to keep them? > > "Damien" wrote: > > > SQLGuru_not wrote: > > > I am running SQL 2000 SP4. > > > I submit the following syntax for the check constraint but when it is in SQL > > > it is changed and all my parantheses are gone except a couple. Weird. > > > Please help. > > > Syntax going in: > > > CONSTRAINT CKFulfillmentPackage > > > CHECK ((ResponsibleForCustomerContactFlg = 1 > > > AND > > > ( > > > (AutomaticPackageGenerationRequestFlg = 0 > > > AND FinancialApplicationContactBeginTs IS NOT NULL > > > AND PackagePreparationTimelinessCd IS NULL > > > ) > > > OR > > > (AutomaticPackageGenerationRequestFlg = 1 > > > AND FinancialApplicationContactBeginTs IS NULL > > > AND PackagePreparationTimelinessCd IS NOT NULL) > > > ) > > > ) > > > OR > > > (ResponsibleForCustomerContactFlg = 0 > > > AND FinancialApplicationContactBeginTs IS NULL > > > AND PackagePreparationTimelinessCd IS NOT NULL)) > > > ) > > > > > > ----------------------------------NOT MISSING PARANTHESES AFTER THE LAST OR > > > STMT------ > > > > > > --Syntax in SQL > > > CHECK ([ResponsibleForCustomerContactFlg] = 1 > > > and ([AutomaticPackageGenerationRequestFlg] = 0 > > > and [FinancialApplicationContactBeginTs] is not null > > > and [PackagePreparationTimelinessCd] is null > > > or [AutomaticPackageGenerationRequestFlg] = 1 > > > and [FinancialApplicationContactBeginTs] is null > > > and [PackagePreparationTimelinessCd] is not null) > > > or [ResponsibleForCustomerContactFlg] = 0 > > > and [FinancialApplicationContactBeginTs] is null > > > and [PackagePreparationTimelinessCd] is not null) > > > ) > > > > Unlike stored procedures, things like constraints are not (I strongly > > believe) stored in their textual form. I believe SQL Server has to > > create different constructs in order to allow it to process constraints > > quickly and efficiently. Therefore, when you ask DMO/SMO to produce a > > script for any of these objects, you'll get a translation back from the > > "compiled" form of the constraint, rather than what you initially > > entered. > > > > What you're getting back is equivalent to your initial statement. SQL > > doesn't need parantheses around the "ands" because, as BOL says: > > "When more than one logical operator is used in a statement, AND > > operators are evaluated first. You can change the order of evaluation > > by using parentheses." > > > > Damien > > > > Just to illustrate the point, here is your original SQL with a little extra
whitespace to help with ease of reading... Now, I find your code easier to follow than the code SQL Server left you with, but nonetheless, if you remove the () surrounding criteria connected with AND, you will not change the way the code works. CONSTRAINT CKFulfillmentPackage CHECK ( ( -- extra paranthesis ResponsibleForCustomerContactFlg = 1 AND ( ( -- extra paranthesis AutomaticPackageGenerationRequestFlg = 0 AND FinancialApplicationContactBeginTs IS NOT NULL AND PackagePreparationTimelinessCd IS NULL ) -- extra paranthesis OR ( -- extra paranthesis AutomaticPackageGenerationRequestFlg = 1 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL ) -- extra paranthesis ) ) -- extra paranthesis OR ( -- extra paranthesis ResponsibleForCustomerContactFlg = 0 AND FinancialApplicationContactBeginTs IS NULL AND PackagePreparationTimelinessCd IS NOT NULL ) -- extra paranthesis ) ) -- unmatched paranthesis? Show quote "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:OWLvjr20GHA.1268@TK2MSFTNGP02.phx.gbl... > Damien's piont is that you are NOT changing the order of operations with > your parenthesis (at least not the ones removed by SQL Server). Although > they make the code more explicit and easier for a person to understand, they > are redundant to SQL Server. The parenthesis that it removed were around > criteria connected by AND, which happens before OR, as if it had parenthesis > around it. > > Your original code is doing exactly the same thing as the code without the > extra parenthesis. The very same order of operations are enforced in both > cases. > > "SQLGuru_not" <SQLGuru***@discussions.microsoft.com> wrote in message > news:B1871A0A-5524-4B44-93F6-C343F08D221A@microsoft.com... > > Damien, thank you for this explanation but as the last part of the BOL > > statement says you can change the order of operation by including > > parantheses. This is where the diconnect is. The parantheses which are > > intended to enforce a specific order of operation are removed by SQL. How > > can I get SQL to keep them? > > > > "Damien" wrote: > > > > > SQLGuru_not wrote: > > > > I am running SQL 2000 SP4. > > > > I submit the following syntax for the check constraint but when it is > in SQL > > > > it is changed and all my parantheses are gone except a couple. Weird. > > > > Please help. > > > > Syntax going in: > > > > CONSTRAINT CKFulfillmentPackage > > > > CHECK ((ResponsibleForCustomerContactFlg = 1 > > > > AND > > > > ( > > > > (AutomaticPackageGenerationRequestFlg = 0 > > > > AND FinancialApplicationContactBeginTs IS NOT NULL > > > > AND PackagePreparationTimelinessCd IS NULL > > > > ) > > > > OR > > > > (AutomaticPackageGenerationRequestFlg = 1 > > > > AND FinancialApplicationContactBeginTs IS NULL > > > > AND PackagePreparationTimelinessCd IS NOT NULL) > > > > ) > > > > ) > > > > OR > > > > (ResponsibleForCustomerContactFlg = 0 > > > > AND FinancialApplicationContactBeginTs IS NULL > > > > AND PackagePreparationTimelinessCd IS NOT NULL)) > > > > ) > > > > > > > > ----------------------------------NOT MISSING PARANTHESES AFTER THE > LAST OR > > > > STMT------ > > > > > > > > --Syntax in SQL > > > > CHECK ([ResponsibleForCustomerContactFlg] = 1 > > > > and ([AutomaticPackageGenerationRequestFlg] = 0 > > > > and [FinancialApplicationContactBeginTs] is not null > > > > and [PackagePreparationTimelinessCd] is null > > > > or [AutomaticPackageGenerationRequestFlg] = 1 > > > > and [FinancialApplicationContactBeginTs] is null > > > > and [PackagePreparationTimelinessCd] is not null) > > > > or [ResponsibleForCustomerContactFlg] = 0 > > > > and [FinancialApplicationContactBeginTs] is null > > > > and [PackagePreparationTimelinessCd] is not null) > > > > ) > > > > > > Unlike stored procedures, things like constraints are not (I strongly > > > believe) stored in their textual form. I believe SQL Server has to > > > create different constructs in order to allow it to process constraints > > > quickly and efficiently. Therefore, when you ask DMO/SMO to produce a > > > script for any of these objects, you'll get a translation back from the > > > "compiled" form of the constraint, rather than what you initially > > > entered. > > > > > > What you're getting back is equivalent to your initial statement. SQL > > > doesn't need parantheses around the "ands" because, as BOL says: > > > "When more than one logical operator is used in a statement, AND > > > operators are evaluated first. You can change the order of evaluation > > > by using parentheses." > > > > > > Damien > > > > > > > > Damien/Jim
Thanks for the insight into what SQL is doing. You are correct in your analysis and it me who is not onboard with the logic. Thanks. Show quote "Jim Underwood" wrote: > Just to illustrate the point, here is your original SQL with a little extra > whitespace to help with ease of reading... > > Now, I find your code easier to follow than the code SQL Server left you > with, but nonetheless, if you remove the () surrounding criteria connected > with AND, you will not change the way the code works. > > CONSTRAINT CKFulfillmentPackage > CHECK > ( > ( -- extra paranthesis > ResponsibleForCustomerContactFlg = 1 > AND > ( > ( -- extra paranthesis > AutomaticPackageGenerationRequestFlg = 0 > AND FinancialApplicationContactBeginTs IS NOT NULL > AND PackagePreparationTimelinessCd IS NULL > ) -- extra paranthesis > OR > ( -- extra paranthesis > AutomaticPackageGenerationRequestFlg = 1 > AND FinancialApplicationContactBeginTs IS NULL > AND PackagePreparationTimelinessCd IS NOT NULL > ) -- extra paranthesis > ) > ) -- extra paranthesis > OR > ( -- extra paranthesis > ResponsibleForCustomerContactFlg = 0 > AND FinancialApplicationContactBeginTs IS NULL > AND PackagePreparationTimelinessCd IS NOT NULL > ) -- extra paranthesis > ) > ) -- unmatched paranthesis? > > > "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message > news:OWLvjr20GHA.1268@TK2MSFTNGP02.phx.gbl... > > Damien's piont is that you are NOT changing the order of operations with > > your parenthesis (at least not the ones removed by SQL Server). Although > > they make the code more explicit and easier for a person to understand, > they > > are redundant to SQL Server. The parenthesis that it removed were around > > criteria connected by AND, which happens before OR, as if it had > parenthesis > > around it. > > > > Your original code is doing exactly the same thing as the code without the > > extra parenthesis. The very same order of operations are enforced in both > > cases. > > > > "SQLGuru_not" <SQLGuru***@discussions.microsoft.com> wrote in message > > news:B1871A0A-5524-4B44-93F6-C343F08D221A@microsoft.com... > > > Damien, thank you for this explanation but as the last part of the BOL > > > statement says you can change the order of operation by including > > > parantheses. This is where the diconnect is. The parantheses which are > > > intended to enforce a specific order of operation are removed by SQL. > How > > > can I get SQL to keep them? > > > > > > "Damien" wrote: > > > > > > > SQLGuru_not wrote: > > > > > I am running SQL 2000 SP4. > > > > > I submit the following syntax for the check constraint but when it > is > > in SQL > > > > > it is changed and all my parantheses are gone except a couple. > Weird. > > > > > Please help. > > > > > Syntax going in: > > > > > CONSTRAINT CKFulfillmentPackage > > > > > CHECK ((ResponsibleForCustomerContactFlg = 1 > > > > > AND > > > > > ( > > > > > (AutomaticPackageGenerationRequestFlg = 0 > > > > > AND FinancialApplicationContactBeginTs IS NOT NULL > > > > > AND PackagePreparationTimelinessCd IS NULL > > > > > ) > > > > > OR > > > > > (AutomaticPackageGenerationRequestFlg = 1 > > > > > AND FinancialApplicationContactBeginTs IS NULL > > > > > AND PackagePreparationTimelinessCd IS NOT NULL) > > > > > ) > > > > > ) > > > > > OR > > > > > (ResponsibleForCustomerContactFlg = 0 > > > > > AND FinancialApplicationContactBeginTs IS NULL > > > > > AND PackagePreparationTimelinessCd IS NOT NULL)) > > > > > ) > > > > > > > > > > ----------------------------------NOT MISSING PARANTHESES AFTER THE > > LAST OR > > > > > STMT------ > > > > > > > > > > --Syntax in SQL > > > > > CHECK ([ResponsibleForCustomerContactFlg] = 1 > > > > > and ([AutomaticPackageGenerationRequestFlg] = 0 > > > > > and [FinancialApplicationContactBeginTs] is not null > > > > > and [PackagePreparationTimelinessCd] is null > > > > > or [AutomaticPackageGenerationRequestFlg] = 1 > > > > > and [FinancialApplicationContactBeginTs] is null > > > > > and [PackagePreparationTimelinessCd] is not null) > > > > > or [ResponsibleForCustomerContactFlg] = 0 > > > > > and [FinancialApplicationContactBeginTs] is null > > > > > and [PackagePreparationTimelinessCd] is not null) > > > > > ) > > > > > > > > Unlike stored procedures, things like constraints are not (I strongly > > > > believe) stored in their textual form. I believe SQL Server has to > > > > create different constructs in order to allow it to process > constraints > > > > quickly and efficiently. Therefore, when you ask DMO/SMO to produce a > > > > script for any of these objects, you'll get a translation back from > the > > > > "compiled" form of the constraint, rather than what you initially > > > > entered. > > > > > > > > What you're getting back is equivalent to your initial statement. SQL > > > > doesn't need parantheses around the "ands" because, as BOL says: > > > > "When more than one logical operator is used in a statement, AND > > > > operators are evaluated first. You can change the order of evaluation > > > > by using parentheses." > > > > > > > > Damien > > > > > > > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics