Range constraint numeric MySQL column: how?

Question

Can I define a constraint on a numeric MySQL-column (InnoDB) to only allow values in a certain range?

For example column wavelength in this table:

CREATE TABLE spectrumdata
(
  valueid INT AUTO_INCREMENT,
  spectrumset INT NOT NULL,
  wavelength DOUBLE NULL,
  intensity DOUBLE NULL,
  error INT NOT NULL,
  status INT NOT NULL,
  PRIMARY KEY (valueid),
  INDEX spectrumset_idx (spectrumset),
  CONSTRAINT spectrumset_fk FOREIGN KEY (spectrumset)
    REFERENCES spectrumsets (setid)
)
COLLATE=utf8_general_ci
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1;

As MySQL is one of the very few DBMS to not support check constraints, your only chance is using a trigger. — user330315
– user330315, Commented Apr 2, 2013 at 7:07
Its ok. try my answer. I don't know the full specs of your table but that might help. — Mark
– Mark, Commented Apr 2, 2013 at 7:11

Community · Accepted Answer · 2017-05-23 12:13:01Z

1

If you are using InnoDB as Engine sir, you can check this out. As you can see, you can create a new table that contains your limiting values and reference to your field (as Foreign Key) it is now then enforce your constraint with referencial integrity.

UPDATE

try this:

   CREATE TABLE allowed_val(
      limiting_val DOUBLE NOT NULL,
      PRIMARY KEY (limiting_val )
    ) ENGINE = InnoDB;

INSERT INTO allowed_val( limiting_val) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),..(1000);

ALTER TABLE spectrumdata
ADD FOREIGN KEY (wavelength) REFERENCES allowed_val(limiting_val);

But you must also alter the spectrumdata wavelength to NOT NULL to DEFAULT = 0; to handle null values.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Apr 2, 2013 at 7:10

Mark

8,47115 gold badges52 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wndrs Over a year ago

Thanks. But this is a workaround, I was wondering if there's a way forcing it by data definition statements, but it seems there isn't. I found the CHECK clause, but the documentation says: "The CHECK clause is parsed but ignored by all storage engines". See: dev.mysql.com/doc/refman/5.6/en/create-table.html

wndrs Over a year ago

Also, your solution doesn't work for double values, only for integers. But thanks anyway

Collectives™ on Stack Overflow

Range constraint numeric MySQL column: how?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related