Search code examples
mysqlsqlansi-sql

SQL constraint for column

I have a column that is called NumberInStock. However, in the database I want to limit the values that can be entered into this column between 1 and 100. What Constraint would I use on the column and would would be the proper syntax of the SQL query? What I got so far is:

ALTER TABLE Inventory
ALTER COLUMN NumberInStock

Now what do I put after that to put the constraint and what constraint would I use

I'm using MySQL that has a 1999 ANSI/ISO standard.

Solution

  • Usually this type of problem should be solved using a CHECK constraint as follows-

    ALTER TABLE Inventory ADD CONSTRAINT chk1 CHECK(NumberInStock BETWEEN 1 AND 100);

    This could have been done in ORACLE But MySQL has a BUG reported regarding the implementation of check constraint.

    Hence u must implement two triggers i.e. one for INSERT and another for UPDATE on that table.