Why are my MySQL default settings not taking effect?

Question

My default value for MOVIE_RATING doesn't seem to work. Can someone tell me what's wrong with the query or help me fix it? Thank you so much.

Create table MOVIE
(
    MOVIE_ID int(7) NOT NULL UNIQUE,
    MOVIE_NAME Varchar(20),
    MOVIE_RELEASE_DATE date,
    MOVIE_RATING Varchar(5) DEFAULT 'NR',
    CHECK (MOVIE_RATING = 'G'OR MOVIE_RATING = 'PG' OR MOVIE_RATING =
    'PG-13' OR MOVIE_RATING =
    'R' OR MOVIE_RATING = 'NC'),
    primary key (MOVIE_ID)
);

Answer 1

@Zolan Noir, you didn't check the Move Rating when the value is 'NR'. So, if you want to use the default value, of course the error will be triggered because you have defined the constraint CHECK on the movie rating field, but there is no 'NR' value when checking.

NB: CHECK will establish a constraint, but there is no check for the value "NR" on the field Movie Rating in your CHECK constraint. Unless you don't add a CHECK to the movie ratings, the code you wrote is correct.

Oh, one last thing, please remove the not null and UNIQUE constraints on the movie ID, since you defined that field as the primary key. This is not useful because the primary key will establish an index constraint, with properties similar to uniqueness and non-nullability.