0

creating the table named temp in MYSQL database.(using WAMP server application) 

create table `temp`
(
        number int(255) not null check(number > 0)
)
engine=innodb;

inserting into a negative value in temp table

INSERT INTO `temp` VALUES (-1);

But the -1 got inserted, i don't why?? is there any problem with the code Result

Tom
  • 83
  • 9
  • Possible duplicate of [CHECK constraint in MySQL is not working](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) – Cfreak Mar 24 '16 at 17:58

3 Answers3

2

The CHECK clause is parsed but ignored by all storage engines. You better use UNSIGNED, which does not allow inserting any signs, i.e. will only allow positive numbers.

create table `temp`
(
        number int(255) unsigned not null 
)
engine=innodb;
Coke
  • 965
  • 2
  • 9
  • 22
  • using this, if i insert a negative value in the table, '0' is inserted in place of it – Tom Mar 24 '16 at 18:10
  • 1
    In a strict sql mode, if you try inserting a negative number, you will get an error saying "ERROR 1264 (22003): Out of range value for column 'number' at row 1". However, you will never get a negative number replaced by zero. – Coke Mar 24 '16 at 18:16
1

MySQL doesn't support check constraints.

It allows them in table creation only for portability support, but they are ignored in reality.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

For this particular case of not negative, you could just use the unsigned int type: 

field INT(10) unsigned not null default '0'
RMT
  • 7,040
  • 4
  • 25
  • 37