MySql Constraint to have particular fields value greater than zero [duplicate]
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.
精彩评论