CODEWITHSUNDEEP
mysqlsql
user1032531
user1032531

Reputation: 26281

Limit SQL columns to given range

A column of datatype integer stores a value which a business rule requires must only be from 0 to 4194303.

How can the database enforce this rule?

Upvotes: 1

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

In most databases, you would use a check constraint. MySQL allows you to declare check constraints but doesn't currently enforce them:

alter table t add constraint chk_t_col check (col between 0 and 4194303);

(More specifically, none of the supported storage engines implement check constraints.)

Instead, you need to use a before insert and before update trigger to validate the value. Perhaps future versions will actually enforce check constraints as well as allowing them to be declared.

If you are not familiar with triggers, you should not just copy code from Stack Overflow. Instead, start by learning about them. A good place to start is the documentation.

Upvotes: 3

Related Questions