Create mysql table limit integer to positive values

Question

I try to create a table with an INTEGER attribute which should be limited to positive numbers. I know there is an UNSIGNED option, but that does the wrong thing. As it allows adding -10 as a value. It will just make a 10 out of it.

Is it possible to deny a wrong entry? I tried using CHECK

DROP TABLE Produkt;
CREATE TABLE Produkt (
    Bezeichnung VARCHAR(237) PRIMARY KEY,
    ProduktNr INTEGER NOT NULL,
    Produktart VARCHAR(3) DEFAULT "XXX",
    CONSTRAINT onlyPositive CHECK(ProduktNr >= 0)
);

But I can still add -10 as a value... What am I doing wrong?

check this answer to understand signed and unsigned http://stackoverflow.com/questions/11515594/when-should-i-use-unsigned-and-signed-int-in-mysql — dev1234, Dec 13 '13 at 09:38
unsigned can not hold negative numbers but it does not prevent you from adding negative ones... They will just be converted to positive. — Chris, Dec 13 '13 at 09:54

score 5 · Accepted Answer · answered Dec 13 '13 at 10:03

5

1) In a strict sql_mode if you define your column as

ProduktNr INT UNSIGNED NOT NULL,

and then try to insert a negative value you'll get an error

ERROR 1264 (22003): Out of range value for column 'ProduktNr' at row 1

Here is SQLFiddle demo. Uncomment insert statement and click Build Schema

2) MySQL still lacks support for CHECK constraints. The CHECK clause is parsed but ignored by all storage engines.

3) On a side note: don't use a VARCHAR(237) column as a PRIMARY KEY, especially if you're using InnoDB engine (all secondary indices on the table also include PK values).

answered Dec 13 '13 at 10:03

peterm

91,357
15
148
157

Why on earth would you implement a feature CHECK and then ignore it? :D Thanks for the sql_mode. That was exactly what I was looking for. – Chris Dec 13 '13 at 10:09
Can i use unsigned on double data type? – Hemanth S R Jan 17 '15 at 12:00

score 0 · Answer 2 · answered Dec 13 '13 at 09:36

0

I believe you can just add the check without naming the constraint. This seemed to work for me:

CREATE TABLE Produkt (
    Bezeichnung VARCHAR(237),
    ProduktNr INTEGER NOT NULL,
    Produktart VARCHAR(3) DEFAULT "XXX",
    PRIMARY KEY (Bezeichnung),
    CHECK(ProduktNr >= 0)
);

I also moved the declaration of the primary key. I'm not 100% certain that you can declare a key the same time as a field, but I did put what I knew.

answered Dec 13 '13 at 09:36

Rogue

11,105
5
45
71

Hi. The naming constraint just helps to keep track if you want to change it later. Nothing else. The system still does not prevent you from adding negative numbers. See http://sqlfiddle.com/#!2/d988a/1 – Chris Dec 13 '13 at 09:59

Create mysql table limit integer to positive values

2 Answers2