CODEWITHSUNDEEP
mysqlmariadb
flameyflocker
flameyflocker

Reputation: 13

MariaDB - Can't get my check constraint to work effectively

So I have this code:

Essentially, I want to be able to insert any type of rating without the requirement of a comment. However, a comment cannot be left unless a rating has been left. For example, insert into movie_rating(movie_quality_rating) values(1); will yield this error: ERROR 4025 (23000): CONSTRAINT movie_rating.theatre_food_comment failed for test_db.movie_rating.

This is the logical I want to accomplish (the rating has to relate to it's relevant comment. ex. movie comment with movie rating:

rating without comment = yes
comment without ratting = no
comment with rating = yes

Sorry if I am wasting anyone's times but I've been stuck on this for days and I'm pretty much a noob and have tried searching everywhere but this is my last resort. I appreciate any ounce of help. Thank you.

Upvotes: 0

Views: 213

Answers (1)

Barmar
Barmar

Reputation: 780724

Use table-level check constraints to test multiple columns.

CREATE TABLE movie_rating (
  customer int NOT NULL AUTO_INCREMENT,
  
  movie_quality_rating int(1) CHECK (movie_quality_rating between 1 and 10),
  movie_quality_comment varchar(255),
  
  theatre_food_rating int(1) CHECK (theatre_food_rating between 1 and 10),
  theatre_food_comment varchar(255),
  
  overall_rating int(1) CHECK (overall_rating between 1 and 10),
  overall_comment varchar(255),

  CHECK (movie_quality_comment IS NULL OR movie_quality_rating IS NOT NULL),
  CHECK (theatre_food_comment IS NULL OR theatre_food_rating IS NOT NULL),
  CHECK (overall_comment IS NULL OR overall_rating IS NOT NULL),
  PRIMARY KEY (customer)
);

Upvotes: 2

Related Questions