CODEWITHSUNDEEP
mysqlsqlansi-sql
Domecraft
Domecraft

Reputation: 1661

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.

Upvotes: 2

Views: 146

Answers (2)

Rajesh Paul
Rajesh Paul

Reputation: 7019

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.

Upvotes: 3

Amir Keshavarz
Amir Keshavarz

Reputation: 3118

Use Check constraint .

ALTER TABLE <TABLE NAME>
ADD CONSTRAINT <CONSTRAINT NAME> CHECK(<PREDICATE> OR/AND/NOT <PREDICATE> ...)

Upvotes: 0

Related Questions