How to restrict a column value in SQLite / MySQL

user_707012 0
Viewed 39015

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.

restriction sqlite mysql
6 Answers

To add some beginner level context to the excellent answer of @NGLN above.

First, one needs to check the foreign key constraint is active, otherwise sqlite won't limit to the input to the column to the reference table:

PRAGMA foreign_key;

...which gives a response of 0 or 1, indicating on or off.

To set the foreign key constraint:

PRAGMA foreign_keys = ON;

This needs to be set to ensure that sqlite3 enforces the constraint.

I found it simplest to just set the primary key of the reference table to be the type. In the OP's example:

CREATE TABLE IF NOT EXISTS vehicle_types(
    vehicle_type text PRIMARY KEY);

Then, one can insert 'car', 'bike' etc into the vehicle_types table (and more in the future) and reference that table in the foreign key constraint in the child table (the table in which the OP wished to reference the type of vehicle):

CREATE TABLE IF NOT EXISTS ops_original_table(
    col_id integer PRIMARY KEY,
    ...many other columns...
    vehicle_type text NOT NULL,
    FOREIGN KEY (vehicle_type) REFERENCES vehicle_types(vehicle_type);

Outwith the scope of the OP's question but also take note that when setting up a foreign key constraint thought should be given to what happens to the column in child table (ops_original_table) if a parent table value (vehicle_types) is deleted or updated. See this page for info

user_147553670
Related