CODEWITHSUNDEEP
mysqlconstraints
Mask
Mask

Reputation: 34217

How to constraint column of MySQL?

like:

create table test(
    score integer unsigned between 1 and 100
...
);

Is it possible to do this in MySQL?

Upvotes: 0

Views: 199

Answers (2)

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21680

Mysql doesn't support CHECK constraints

You can create BEFORE UPDATE/INSERT TRIGGER with RAISE ERROR

EDIT: Raise Error with

SET new.score = 1 / 0;

Something like that:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    IF  NEW.score < 1 OR NEW.score > 100
              SET NEW.score = 1 / 0; 
  END;

Upvotes: 1

miku
miku

Reputation: 188164

In short: MySQL does not support CHECK constraints, although for compatibility with other engines it will parse them (but ignores them).

See: MySQL and Check Constraints

Upvotes: 0

Related Questions