Add a tuple-based CHECK constraint to a table

Asked Apr 15 '14 at 02:53

Active Apr 15 '14 at 19:09

Viewed 1,153 times

I have a table PC that has 5 columns:

CREATE TABLE PC 
(
    model VARCHAR(25),
    speed FLOAT,
    ram, INT,
    hd INT,
    price FLOAT,
    PRIMARY KEY (model)
);

I have to add a tuple-based CHECK constraint to verify that a PC with a speed less than 2.0 must not sell for more than $600.

So far, what I have is:

ALTER TABLE PC
ADD CONSTRAINT pc_chk
CHECK (
    SELECT price FROM PC WHERE speed < 2.0
) < 600;

This yields an error in MySQL so clearly I am doing something wrong. Can someone give me a hint?

edited Apr 15 '14 at 19:09

user247702

asked Apr 15 '14 at 02:53

Triwis

It is an assignment question. I mentioned it in the title but it was edited – Triwis Apr 15 '14 at 03:24
1

Would be a simple `CHECK( speed >= 2.0 OR price <= 600 )` but unfortunately mysql doesn't support check constraints. They are parsed for compatibility reasons, but completely ignored. – piotrm Apr 15 '14 at 03:27
Is it pure MySQL? You can do this validation in frontend. However it is possible to create a [`TRIGGER`](http://dev.mysql.com/doc/refman/5.0/en/triggers.html). – Mark Apr 15 '14 at 03:28
@piotrm, it is a theory question, so if you think `CHECK( speed >= 2.0 OR price <= 600 )` would do the trick then that's perfect – Triwis Apr 15 '14 at 03:41
The assignment is impossible to solve with MySQL because it does not support check constraints. – Apr 15 '14 at 19:14

0 Answers0