CODEWITHSUNDEEP
mysqlintegerrange
Nick
Nick

Reputation: 886

SQL Integer Range When Creating Tables

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.

Upvotes: 5

Views: 31586

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26353

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

Upvotes: 11

Related Questions