MySql Constraint to have particular fields value greater than zero

Question

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

Is P_Id supposed to be a primary key? If so, you're better off letting MySQL handle it using `PRIMARY KEY (`P_Id`)` with `AUTO_INCREMENT` as an option on P_Id. — KushalP, Apr 23 '11 at 13:43
yes, UNSIGNED INT worked, but i want to make use of Constraints — Kalpesh Jain, Apr 23 '11 at 14:13

score 2 · Accepted Answer · answered Apr 23 '11 at 14:11

2

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

answered Apr 23 '11 at 14:11

Nicola Cossu

54,599
15
92
98

The link seems dead now. – Nae Jan 02 '19 at 08:46

score 1 · Answer 2 · edited Feb 20 '19 at 20:04

1

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.

edited Feb 20 '19 at 20:04

YesThatIsMyName

1,585
3
23
30

answered Feb 20 '19 at 15:57

druid62

109
3

I now prefer it like this: `\`CHECK_id\` tinyint unsigned NOT NULL AS (IF(id>0,1,-1))`, because that leads to a clearer error message. – druid62 Jun 24 '19 at 11:44

MySql Constraint to have particular fields value greater than zero

2 Answers2