23

I would like to restrict a column value in a SQL table. For example, the column values can only be "car" or "bike" or "van". My question is how do you achieve this in SQL, and is it a good idea to do this on the DB side or should I let the application restrict the input.

I also have the intention to add or remove more values in the future, for example, "truck".

The type of Databases I am using are SQLite and MySQL.

Achraf Almouloudi
  • 756
  • 10
  • 27
Maro
  • 4,015
  • 7
  • 32
  • 34

5 Answers5

36

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.

NGLN
  • 41,953
  • 8
  • 104
  • 194
  • 2
    I like this the best - it's the only answer that fulfils the requirement of "adding more type in the future" without making db changes (just insert a new row) – Bohemian Jun 16 '11 at 04:40
  • Agreed, the fact the data is not static confirms it should be a lookup table rather than a `CHECK` constraint or equivalent. And yes, this data constraint should be modelled in the database. – onedaywhen Jun 16 '11 at 05:56
27

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.

nageeb
  • 1,992
  • 1
  • 12
  • 25
  • 2
    This hardly fulfils the requirement of "adding more type in the future" easily - you have to make a db change. Ask the DBA how thrilled he'll be about that. – Bohemian Jun 16 '11 at 04:41
  • 5
    You didn't mention that you didn't want to bother the poor DBA with adding more items. Maybe you should have specified that in the question. – nageeb Jun 16 '11 at 05:13
  • I didn't ask the question. I was just commenting on your answer. I didn't downvote though :) – Bohemian Jun 16 '11 at 07:24
  • 1
    My mistake. I apologize if the comment sounded a bit snarky, but I guess that different people take different definitions. I 100% agree with the accepted answer (and upvoted it), and find that programmatically allowing the addition of values would be the best solution, but being a coder personally, I would end up just modifying the DB if it weren't an end-user facing feature. – nageeb Jun 16 '11 at 21:38
  • 1
    When implementing this with an ORM layer this can easily be changed via migrations. Propperly tracked via git I find this to be a good solution to implement and keep track of value restrictions implemented in the database itself. – Daniel Böttner Jan 21 '19 at 13:10
10

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')
);
Alex Aza
  • 73,821
  • 25
  • 151
  • 132
  • In SQLite, you can say `check("name" in ('car', 'bike', 'van'))`. Also:"-quotes are for identifiers and '-quotes for literal values. – equaeghe Mar 23 '16 at 09:15
5

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.

Bob Probst
  • 9,305
  • 8
  • 31
  • 41
2

If you want to go with DB-side validation, you can use triggers. See this for SQLite, and this detailed how-to for MySQL.

So the question is really whether you should use Database validation or not. If you have multiple clients -- whether they are different programs, or multiple users (with possibly different versions of the program) -- then going the database route is definitely best. The database is (hopefully) centralized, so you can decouple some of the details of validation. In your particular case, you can verify that the value being inserted into the column is contained in a separate table that simply lists valid values.

On the other hand, if you have little experience with databases, plan to target several different databases, and don't have the time to develop expertise, perhaps simple application level validation is the most expedient choice.

Dilum Ranatunga
  • 13,104
  • 3
  • 40
  • 50