1 
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.

Improve this question
1
  • Your check contraint resolves to accepting only these 2 conditions you defined. Commented Oct 21, 2021 at 5:17

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments