Search code examples
mysqlcheck-constraints

MySql Constraint to have particular fields value greater than zero

I have the following MySql syntax to create a table with constraint to check P_Id column have value greater than zero, but it still allows me to add values less than 0 like -1,-2, etc.

CREATE TABLE Persons(
    P_Id int NOT NULL ,
    LastName varchar( 255 ) NOT NULL ,
    FirstName varchar( 255 ) ,
    Address varchar( 255 ) ,
    City varchar( 255 ) ,
    CHECK (
        P_Id >0
    )
)

Is there anything that i am doing wrong in above structure to have value for P_Id > 0 ??

Solution

  • Check constraints don't work in mysql. You have to make some trick to emulate them. Take a look at this article

    http://forge.mysql.com/wiki/Triggers#Emulating_Check_Constraints