MySql Constraint to have particular fields value greater than zero [duplicate]

This question already has an开发者_运维知识库swers here: CHECK constraint in MySQL is not working (8 answers) Closed 4 years ago.

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 ??

---

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

---

You could add a derived

tinyint NOT NULL

column called

id_check

with (for example) the formula

(IF(id<1,NULL,1))

(many other variants feasible)

Be aware that column names are non-case-sensitive, so best lowercase them.