Как ограничить значение столбца в SQLite / MySQL

Question

Я хотел бы ограничить значение столбца в таблице SQL. Например, значениями столбца могут быть только "автомобиль", "велосипед" или "фургон". Мой вопрос заключается в том, как добиться этого в SQL, и это хорошая идея сделать это на стороне БД, или я должен позволить приложению ограничить ввод.

У меня также есть намерение добавить или удалить больше значений в будущем, например, "грузовик"

Типы баз данных, которые я использую: SQLite и MySQl

Answer 1

Добавьте новую таблицу, содержащую эти транспортные средства, и сделайте свой столбец внешним ключом для этой таблицы. Новые транспортные средства могут быть добавлены в таблицу в будущем, и определение вашего столбца остается прежним.

С этой конструкцией я бы определенно решил регулировать это на уровне БД, а не на уровне приложения.

Answer 2

Для MySQL вы можете использовать тип данных ENUM.

column_name ENUM ("маленький", "средний", "большой")

См. MySQL Reference: Тип ENUM

Чтобы добавить к этому, я считаю, что всегда лучше ограничить на стороне БД И на стороне приложения. Enum плюс поле выбора, и вы закрыты.

Answer 3

Да, рекомендуется добавить проверочные ограничения. Проверочные ограничения используются для обеспечения достоверности данных в базе данных и обеспечения целостности данных. Если они используются на уровне базы данных, приложения, использующие базу данных, не смогут добавлять недопустимые данные или изменять действительные данные, чтобы данные становились недействительными, даже если само приложение принимает недопустимые данные.

В SQLite:

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

В MySQL:

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

Answer 4

Вы бы использовали ограничение проверки. В SQL Server это работает так

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

Я не уверен, что это стандарт ANSI, но я уверен, что MySQL имеет аналогичную конструкцию.

Answer 5

Если вы хотите пройти проверку на стороне DB, вы можете использовать триггеры. Смотрите это для SQLite, и это подробные инструкции для MySQL.

Таким образом, вопрос заключается в том, следует ли использовать проверку базы данных или нет. Если у вас есть несколько клиентов - будь то разные программы или несколько пользователей (возможно, с разными версиями программы) - тогда путь к базе данных определенно лучше. База данных (надеюсь) централизована, поэтому вы можете отделить некоторые детали проверки. В вашем конкретном случае вы можете убедиться, что значение, вставляемое в столбец, содержится в отдельной таблице, в которой просто перечислены допустимые значения.

С другой стороны, если у вас мало опыта работы с базами данных, вы планируете работать с несколькими различными базами данных и у вас нет времени на развитие опыта, возможно, простейшая проверка на уровне приложений является наиболее целесообразным выбором.

Answer 6

Чтобы добавить некоторый контекст начального уровня к отличному ответу @NGLN выше.

Во-первых, нужно проверить, активно ли ограничение внешнего ключа, иначе sqlite не будет ограничивать ввод в столбец справочной таблицы:

      PRAGMA foreign_key;

... который дает ответ 0 или 1, указывая на включение или выключение.

Чтобы установить ограничение внешнего ключа:

      PRAGMA foreign_keys = ON;

Это необходимо установить, чтобы гарантировать, что sqlite3 применяет ограничение.

Я нашел, что проще всего просто установить первичный ключ справочной таблицы как тип. В примере OP:

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

Затем можно вставить «автомобиль», «велосипед» и т. д. в таблицу Vehicle_types(и многое другое в будущем) и сослаться на эту таблицу в ограничении внешнего ключа в дочерней таблице (таблице, в которой OP хотел сослаться на тип транспортное средство):

      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);

Вне рамок вопроса OP, но также обратите внимание, что при настройке ограничения внешнего ключа следует подумать о том, что происходит со столбцом в дочерней таблице (ops_original_table), если значение родительской таблицы (vehicle_types) удаляется или обновляется. Смотрите эту страницу для информации