CHAR field may only have certain values - CONTRAINT

Question

How do I make a constraint in a table for a field (CHAR(20)) that it may only accept, for example, "car" or "bike".

CREATE TABLE Things (
id INTEGER NOT NULL,
thing CHAR(30) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT thingcheck CHECK (thing = "car" OR thing = "bike")
);

If I insert (1, "laptop") it still inserts! Any ideas?

Thanks very much for your time.

--- EDIT ---

As John pointed out, MySQL ignored CHECK clauses. Thanks!

Why you dont use enum field type? [enum](http://dev.mysql.com/doc/refman/5.0/en/enum.html) — neworld, Oct 11 '11 at 00:52

score 3 · Accepted Answer · answered Oct 11 '11 at 00:51

3

how about:

ENUM('car', 'bike')

answered Oct 11 '11 at 00:51

voldomazta

1,300
1
10
19

score 0 · Answer 2 · answered Oct 11 '11 at 03:01

0

You could create a table that contains a list of the allowed values, and then set up a FOREIGN KEY constraint against it. But, really, why not just use an ENUM?

answered Oct 11 '11 at 03:01

Ilmari Karonen

49,047
9
93
153

score 0 · Answer 3 · answered Oct 11 '11 at 05:54

0

How about this,

CREATE TABLE Things (
id INTEGER NOT NULL,
thing CHAR(30) NOT NULL CHECK (thing = 'car' or thing='bike'),
PRIMARY KEY (id),
);

You should give the strings in single quote !!

answered Oct 11 '11 at 05:54

Jithu

511
4
11
26

The quotes don't matter (except in [`ANSI_QUOTES` mode](http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_ansi_quotes)), and [`CHECK` still does nothing](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-not-working). – Ilmari Karonen Oct 11 '11 at 06:08

CHAR field may only have certain values - CONTRAINT

3 Answers3