none
SQL Server Changes CHECK constraint with IN/NOT IN RRS feed

  • Question

  • When I add a CHECK constraint in SQL Server it seems to get changed by the database. For example

    • ALTER TABLE [Check] ADD CONSTRAINT CHECK_Check_InColumn CHECK ([InColumn] IN ( 1 , 2 , 3 ))
    • ALTER TABLE [Check] ADD CONSTRAINT CHECK_Check_LongColumn CHECK ([LongColumn] NOT IN ( 1000 , 2000 , 3000 ))

    Become

    • ([InColumn]=(3) OR [InColumn]=(2) OR [InColumn]=(1))
    • (NOT ([LongColumn]=(3000) OR [LongColumn]=(2000) OR [LongColumn]=(1000)))

    Respectively. Now I know there must be some optimization going on but as I'm mainly concerend with moving schemas between databases this means what I put in isn't what I get out. Can I forced SQL Server to give me back the actual CHECK constraint I added or stop SQL Server from changing it.

    This question refers to 2005 but I guess it also applies to 2008 and 2008 R2 as well.

    Thanks for any help

    Colin

    Saturday, March 5, 2011 10:55 AM

Answers

All replies

  •  

    Hi mxcolin,

    Thanks for your post.

    According to your description, I check your T-sql in my SSMS, then I go to "Script table as" to see it become as what you said. I think it doesn't relate to Providers, it is optimized by SQL. I think you should repost your question to : http://social.msdn.microsoft.com/Forums/en/category/sqlserver

    By the way, If I misunderstand, please feel free to let me know.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by mxcolin Thursday, March 10, 2011 10:05 PM
    Monday, March 7, 2011 6:20 AM
    Moderator
  • Hi Colin,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 10, 2011 3:12 AM
    Moderator