Difficulty using check/constraint in SQL?

1

CREATE TABLE IF NOT EXISTS flatpackstuff (

FLATPACKID int AUTO_INCREMENT,
NAME varchar (20) NOT NULL,
COLOUR varchar (20),
TYPE ENUM ("Office", "Kitchen", "Bedroom", "General"),
UNITPRICE decimal CHECK (UNITPRICE BETWEEN 5.00 and 500.00)
PRIMARY KEY (FLATPACKID));

I am trying to ensure that UNITPRICE is between 5.00 and 500.00 but I am getting errors.

A comma or closing bracket was expected near CHECK
Unexpected beginning of a statement near UNITPRICE
Unrecognised statement type (near BETWEEN)

Where am I going wrong

2

Sounds like it's MySQL but this site is for Microsoft SQL Server
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

Maybe you do not need to specify the column name in the the CHECK?

CREATE TABLE t1
(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);
3

Think it needs a comma at the end of the line with the check
Apart from that check that the version you are using supports check constraints and that format.

1 Like