locked
Condition Insert according to a specific value RRS feed

  • Question

  • Hello,

    I have a "Table1" that has a "Column1" that has float values, is it possible to condition the insertion of rows to the table from the table design, so that, I will be allow only to insert rows where the values of Column1 would be greater than 0.5, for example?

    It is to say, even if I execute the correct SQL command to INSERT with value of 0 for Column1 I would like to get an error from the database saying that I tried to insert a value that is lower than 0.5 for example.

    Thanks


    Kikeman Electric Systems Engineer
    • Edited by Kikeman Wednesday, December 21, 2011 5:08 PM
    Wednesday, December 21, 2011 5:08 PM

Answers

  • Hello,
    You can do this with CHECK constraint or via a trigger. If you need a custom message to be shown by the database then you'll need trigger. If you just want to be sure that incorrect values won't be inserted and don't care about exact error message then CHECK constraint is your choice.
    Here is the example of CHECK constraint:
    alter table <your table name> add constraint CK_Column1 check(Id > 0.5)
    
    • Proposed as answer by Uri DimantMVP Thursday, December 22, 2011 8:15 AM
    • Marked as answer by Kikeman Friday, December 23, 2011 8:19 PM
    Thursday, December 22, 2011 7:30 AM
  • In SSMS for 2008 Standard (do not have express version, if different), you right-click on the table and select "Design" from the popup menu.  The toolbar will be updated, and from there you click on the Manage Check Constraints button.  BTW - the float datatype is approximate and some numbers cannot be represented exactly.  You may need to adjust the value used in the constraint to reflect this. 

    It would be a good experience for you (and much simpler/faster in the future) to learn the alter table syntax and write the constraint creation manually. 

    • Marked as answer by Kikeman Friday, December 23, 2011 8:18 PM
    Friday, December 23, 2011 7:28 PM

All replies

  • Hello,
    You can do this with CHECK constraint or via a trigger. If you need a custom message to be shown by the database then you'll need trigger. If you just want to be sure that incorrect values won't be inserted and don't care about exact error message then CHECK constraint is your choice.
    Here is the example of CHECK constraint:
    alter table <your table name> add constraint CK_Column1 check(Id > 0.5)
    
    • Proposed as answer by Uri DimantMVP Thursday, December 22, 2011 8:15 AM
    • Marked as answer by Kikeman Friday, December 23, 2011 8:19 PM
    Thursday, December 22, 2011 7:30 AM
  • Can this be done directly from the "SQL Server Management Studio" from table desing?
    Kikeman Electric Systems Engineer
    Thursday, December 22, 2011 2:34 PM
  • Yes it can be
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 22, 2011 2:46 PM
  • How :)

    I have been searching from a propertie called "check" but I am not able to find it, I am using SQL Server 2008 Express.

    Thanks for your help.


    Kikeman Electric Systems Engineer
    Thursday, December 22, 2011 2:54 PM
  • In SSMS for 2008 Standard (do not have express version, if different), you right-click on the table and select "Design" from the popup menu.  The toolbar will be updated, and from there you click on the Manage Check Constraints button.  BTW - the float datatype is approximate and some numbers cannot be represented exactly.  You may need to adjust the value used in the constraint to reflect this. 

    It would be a good experience for you (and much simpler/faster in the future) to learn the alter table syntax and write the constraint creation manually. 

    • Marked as answer by Kikeman Friday, December 23, 2011 8:18 PM
    Friday, December 23, 2011 7:28 PM