如何在SQLite中创建ENUM类型?

I need to convert a table from MySQL to SQLite, but I can't figure out how to convert an enum field, because I can't find ENUM type in SQLite.

我需要将一个表从MySQL转换为SQLite，但我不知道如何转换enum字段，因为我不能在SQLite中找到enum类型。

The aforementioned field is pType in the following table:

上述字段为下表中的pType:

CREATE TABLE `prices` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `pName` VARCHAR(100) NOT NULL DEFAULT '',
    `pType` ENUM('M','R','H') NOT NULL DEFAULT 'M',
    `pField` VARCHAR(50) NULL DEFAULT NULL,
    `pFieldExt` VARCHAR(50) NULL DEFAULT NULL,
    `cmp_id` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

I need a field with only three values for the user to chose, and I would like to enforce that in the DB, not just in my application.

我需要一个只有三个值的字段供用户选择，我希望在DB中执行，而不仅仅是在我的应用程序中。

3 个解决方案

#1

There is no enum type in SQLite, only the following:

SQLite中没有enum类型，只有以下几点:

NULL
零
INTEGER
整数
REAL
真正的
TEXT
文本
BLOB
团

Source: http://www.sqlite.org/datatype3.html

来源:http://www.sqlite.org/datatype3.html

I'm afraid a small, custom enum table will be required in your case.

恐怕在您的情况下需要一个小的，自定义的枚举表。

#2

SQLite way

SQLite的方式

CREATE TABLE prices (
 id INTEGER PRIMARY KEY,
 pName TEXT CHECK( LENGTH(pName) <= 100 ) NOT NULL DEFAULT '',
 pType TEXT CHECK( pType IN ('M','R','H') ) NOT NULL DEFAULT 'M',
 pField TEXT CHECK( LENGTH(pField) <= 50 ) NULL DEFAULT NULL,
 pFieldExt TEXT CHECK( LENGTH(pFieldExt) <= 50 ) NULL DEFAULT NULL,
 cmp_id INTEGER NOT NULL DEFAULT '0'
)

#3

For others coming to this in the future, to expand on MPelletier’s answer, you can create the tables as:

对于未来的其他人来说，要扩展MPelletier的答案，你可以创建表格:

CREATE TABLE Price (
  PriceId INTEGER       PRIMARY KEY AUTOINCREMENT NOT NULL,
  Name    VARCHAR(100)  NOT NULL,
  Type    CHAR(1)       NOT NULL DEFAULT ('M') REFERENCES PriceType(Type)
);

CREATE TABLE PriceType (
  Type    CHAR(1)       PRIMARY KEY NOT NULL,
  Seq     INTEGER
);
INSERT INTO PriceType(Type, Seq) VALUES ('M',1);
INSERT INTO PriceType(Type, Seq) VALUES ('R',2);
INSERT INTO PriceType(Type, Seq) VALUES ('H',3);

Done like this, the enumeration values are available directly in the Price table as they would be using an ENUM: you don’t need to join to the PriceType table to get the Type values, you only need to use it if you want to determine the sequence of the ENUMs.

这样做，枚举值可以直接在Price表中使用，因为它们将使用ENUM:您不需要连接到PriceType表来获得类型值，只要您想确定枚举的序列，您只需要使用它。

Foreign key constraints were introduced in SQLite version 3.6.19.

在SQLite版本3.6.19中引入了外键约束。

#1