0

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

GMB
  • 216,147
  • 25
  • 84
  • 135
NotVeryBright
  • 15
  • 5
  • 1
    Why would I rate a restaurant 3.14159265358979323846264338 or something instead of 3 or 4? – Salman A Nov 04 '20 at 08:51
  • Does this answer your question? [Want to restrict the value of a MySQL field to specific range (Decimal values)](https://stackoverflow.com/questions/9575604/want-to-restrict-the-value-of-a-mysql-field-to-specific-range-decimal-values) – Ivar Nov 04 '20 at 08:55
  • @SalmanA, they have perfectly round pizzas, however average tasting. – jarlh Nov 04 '20 at 09:14
  • @SalmanA it's not that. Its meant to be 3.25, two decimals not... as many as u did xD – NotVeryBright Nov 04 '20 at 12:24
  • Don't use `double for this. Use `numeric`/`decimal`. – Gordon Linoff Nov 04 '20 at 13:01

1 Answers1

1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135