基于字段值添加唯一约束 [英] Add unique constraint based on field value

问题描述

对于下表：

我想添加一个约束，如果IsBanned标志设置为true，BannedOn字段不能为空（不能设置为null）。

I'd like to add a constraint that if IsBanned flag is set to true, the BannedOn field cannot be left empty (cannot be set to null).

如何在MySQL中执行此操作？这里是我的CREATE语法：

How can I do this in MySQL? Here's my CREATE syntax:

CREATE TABLE IF NOT EXISTS `fa_ranking_system`.`Player` (
  `PlayerID` INT NOT NULL AUTO_INCREMENT,
  `FK_ServerID` INT NOT NULL,
  `PlayerName` VARCHAR(15) NOT NULL,
  `RegDate` DATETIME NOT NULL,
  `IsBanned` TINYINT(1) NOT NULL,
  `LastUpdatedOn` DATETIME NOT NULL,
  `LastUpdatedBy` VARCHAR(20) NOT NULL,
  `BannedOn` DATETIME NULL,
  PRIMARY KEY (`PlayerID`, `FK_ServerID`),
  INDEX `fk_Player_Server_idx` (`FK_ServerID` ASC),
  CONSTRAINT `fk_Player_Server`
    FOREIGN KEY (`FK_ServerID`)
    REFERENCES `fa_ranking_system`.`Server` (`ServerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

推荐答案

实际上，您可以不在DDL语法中定义条件结构。您的字段可以是 NULL 或 NOT NULL - 没有第三个选项（它不能依赖另一个字段 in structure ）

Actually, you can not define conditional structures in DDL syntax. Your field can be either NULL or NOT NULL - there's no third option (and it cannot depend of another field in structure)

但您仍然可以通过触发器模拟所需的行为。如果传入的数据在逻辑上无效，可以中断 UPDATE / INSERT 语句。这可以通过以下方式完成：

But you can still emulate desired behavior via triggers. You can interrupt UPDATE/INSERT statement if incoming data is invalid in terms of your logic. That can be done via:

CREATE TRIGGER `bannedOnCheck`
BEFORE INSERT ON `fa_ranking_system`.`Player`
FOR EACH ROW
BEGIN
  IF(new.IsBanned && new.BannedOn IS NULL) THEN
    SIGNAL 'Integrity check failed: can not set banned without ban date'
  END IF
END

这篇关于基于字段值添加唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！