SQL Integer Range When Creating Tables

Question

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.

SiteID in this case refers to the authority level someone in the Site has, where 1 = Admin, 2 = second most power, 3 = 3rd most power, 4 = 4th most power. — Nick

Ed Gibbs Ed Gibbs · Accepted Answer · 2013-04-18T02:31:18

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

SQL Integer Range When Creating Tables

1 Answers