范围约束数字 MySQL 列:如何? [英] Range constraint numeric MySQL column: how?

问题描述

我可以对数字 MySQL 列 (InnoDB) 定义约束以仅允许特定范围内的值吗?

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

例如此表中的列wavelength:

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;

推荐答案

如果您使用 InnoDB 作为引擎先生，您可以 检查这个.如您所见，您可以创建一个新表，其中包含您的限制值和对您的字段的引用(作为外键)，然后通过引用完整性强制执行您的约束.

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.

更新

试试这个:

   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);

但是您还必须将 spectrumdata 波长更改为 NOT NULL 到 DEFAULT = 0；处理空值.

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

这篇关于范围约束数字 MySQL 列:如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！