5

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Nick
  • 882
  • 2
  • 9
  • 31

1 Answers1

11

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
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • It looks like CHECK doesn't actually work in MySQL: "CHECK clause is parsed but ignored by all storage engines." https://dev.mysql.com/doc/refman/5.0/en/create-table.html – Jack O'Connor Oct 28 '15 at 17:26
  • You're right Jack, and thanks for pointing this out. I don't recall answering this question because it's been so long, but around that time I was just dabbling in MySQL after years of Oracle and SQL Server. I found out later that it didn't support CHECK constraints. Found it hard to believe, but 2+ years on it still doesn't. Curious, because I'd think it would be easier to implement than PK or FK. Still, the last option (the `ENUM`) works. I've added a disclaimer. – Ed Gibbs Oct 29 '15 at 02:49