Search code examples
mysqlsqlcreate-tablecheck-constraints

How would i make a double that doesn't go higher than 5?

I want to make a double in a table, lets say its a restaurant for example

   create table Restaurant(
    RestaurantName varchar(17),
    rating double?
    );

Basically if you wanted it to be rated out of 5 the Restaurant, how would you go on doing that? So it could be like 4.25 or 3.91 or anything from 0-5.0

Solution

  • In MySQL 8.0, you can use a check constraint:

    create table restaurant(
    restaurantname varchar(17),
    rating decimal(5, 4) check(rating between 0 and 5)
);

    You probably don't need double precision to store a rating. I changed the datatype to a decimal, with 4 decimal positions.

    I also assumed that you want a value between 0 and 5 inclusive. If you wanted to exclude the upper bound, then:

        rating decimal(5, 4) check(rating >= 0 and rating < 5)

    In earlier versions, you would typically need to implement the logic with a trigger.