How to restrict a column value in SQLite / MySQL

Yes, it is recommended to add check constraints. Check constraints are used to ensure the validity of data in a database and to provide data integrity. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomes invalid, even if the application itself accepts invalid data.

In SQLite:

create table MyTable
(
    name string check(name = "car" or name = "bike" or name = "van")
);

In MySQL:

create table MyTable
(
    name ENUM('car', 'bike', 'van')
);

You would use a check constraint. In SQL Server it works like this

ALTER TABLE Vehicles
ADD CONSTRAINT chkVehicleType CHECK (VehicleType in ('car','bike','van'));

I'm not sure if this is ANSI standard but I'm certain that MySQL has a similar construct.


Add a new table containing these means of transport, and make your column a foreign key to that table. New means of transport can be added to the table in future, and your column definition remains the same.

With this construction, I would definitively choose to regulate this at the DB level, rather than that of the application.


For MySQL, you can use the ENUM data type.

column_name ENUM('small', 'medium', 'large')

See MySQL Reference: The ENUM Type

To add to this, I find it's always better to restrict on the DB side AND on the app side. An Enum plus a Select box and you're covered.