MYSQL - How to restrict certain values using Constraint?-CodePudding

CREATE TABLE Movie(
name VARCHAR(30),
year int,
CHECK ((name='one' AND year>1990) OR (name='two' AND year<1990))
);

I'm a beginner in MySQL and my goal here is to restrict name='one' and name='two' so that their year cannot be after or before 1990 on insert values. The constraint works fine on 'one' and 'two' but when I try to add name='three' for example, it throws an error and I couldn't figure out why since name='three' is not in the constraint and shouldn't be restricted to any year.

mysql> INSERT INTO Movie values ('three', 1999);
ERROR 3819 (HY000): Check constraint 'movie_chk_1' is violated.

If you have any tips please tell me since I don't have much experience with databases.

CodePudding user response：

I suspect that you want to allow any names besides one and two with no additional restrictions. If so, then you need to add a condition for that to your check constraint.

CREATE TABLE Movie (
    name VARCHAR(30),
    year INT,
    CHECK (name = 'one' AND year > 1990 OR name = 'two' AND year < 1990 OR
          name NOT IN ('one', 'two'));

So the logic here is to allow:

name = one and years greater than 1990
name = two and years less than 1990
or any name other than one and two, for any year