创建表时的SQL整数范围 [英] SQL Integer Range When Creating Tables

问题描述

我正在尝试在我的一条create语句中给我的INT提供一系列可能的值，即

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.

推荐答案

作者注:此答案的前两个部分不正确.我以为MySQL支持 CHECK 约束，但事实并非如此.仍然没有.要将列限制为简单的值列表，请在此答案末尾使用 ENUM 方法.如果逻辑更复杂(值范围，基于另一列的值等)，则唯一的MySQL选项是触发器.

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.

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

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

或者:

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

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

Or if you can live with a string SiteID then:

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

这篇关于创建表时的SQL整数范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！