创建表时的 SQL 整数范围

我试图在我的一个创建语句中为我的 INT 提供一系列可能的值，即

<上一页>创建表站点(**SiteID INT (1,4),**UserID INT UNSIGNED 不为空，名称 varchar(128) 唯一非空，外键(UserID)引用用户(UserID)，主键 (SiteID));

我忘记了您用于范围的语法，而且我很确定我在尝试按原样使用它时会出错.

解决方案

作者注:这个答案的前两部分是错误的.我认为 MySQL 支持 CHECK 约束，但它没有.还是不行.要将列限制为简单的值列表，请在此答案末尾使用 ENUM 方法.如果逻辑更复杂(值范围、基于另一列的值等)，唯一的 MySQL 选项是触发器.

<小时>

如果是 INT，则需要 CHECK 约束:

创建表站点 (站点ID INT，CONSTRAINT SiteID_Ck CHECK (SiteID IN (1, 2, 3, 4)),……其余的

或者:

创建表站点 (站点ID INT，CONSTRAINT SiteID_Ck CHECK (SiteID BETWEEN 1 和 4),……其余的

或者，如果您可以使用字符串 SiteID，那么:

创建表站点 (SiteID ENUM('1', '2', '3', '4'),……其余的

I'm trying to give my INT in one of my create statements a range of possible values, i.e.

CREATE TABLE Site(
    **SiteID INT (1,4),**
    UserID INT UNSIGNED Not Null,
    Name varchar(128) Unique Not Null,
    Foreign Key (UserID) References Users(UserID),
    Primary key (SiteID)
);

I forget the syntax that you use for ranges, and I'm pretty sure I'm erring when I'm attempting to use it as is.

解决方案

Author's Note: the first two parts of this answer are incorrect. I thought MySQL supported CHECK constraints and it didn't. Still doesn't. To limit columns to a simple list of values, use the ENUM approach at the end of this answer. If the logic is more complicated (range of values, value based on another column, etc.), the only MySQL option is a trigger.

---

You need a CHECK constraint if it's an INT:

CREATE TABLE Site (
  SiteID INT,
  CONSTRAINT SiteID_Ck CHECK (SiteID IN (1, 2, 3, 4)),
  ... and the rest

Or:

CREATE TABLE Site (
  SiteID INT,
  CONSTRAINT SiteID_Ck CHECK (SiteID BETWEEN 1 AND 4),
  ... and the rest

Or if you can live with a string SiteID then:

CREATE TABLE Site (
  SiteID ENUM('1', '2', '3', '4'),
  ... and the rest