mysql - MYSQL 来自两列的唯一性（交叉唯一性）

Question

我有一个包含 2 列'NAME'和'ALIAS' 的表。我希望两列的内容都是唯一的。所以我不能创建一个已经在“别名”中的“名称” ，反之亦然。

'NAME' B 是不允许的，因为 B 先前已在列 'ALIAS' 中使用过；

'ALIAS' A 是不允许的，因为 A 以前在列 'NAME' 中使用过；

'NAME' C 是允许的，因为它以前从未在'NAME' 列和'ALIAS' 列中使用过，'ALIAS' D 是允许的，因为它以前从未使用过，在'NAME' 列和“别名”列；

...

我希望列 'NAME' 和列 'ALIAS' 中的数据在一个列中是唯一的

这个方法我试过了

CREATE TABLE `Group` (
  Name VARCHAR(50) NOT NULL DEFAULT '',
  Alias VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (Name)
)
ENGINE = MYISAM,
CHECKSUM = 0;

ALTER TABLE `Group` ADD CONSTRAINT Constraint_Group UNIQUE KEY(`Name`, `Alias`);

但唯一性仅适用于相应的列。

有没有办法得到它？

谢谢。

标签： mysqlunique

Answer 1

从@Michael 得到答案 -此线程中的 sqlbot

使用触发器来满足我的需求。

DROP TABLE IF EXISTS Groups;

CREATE TABLE groups (
  Name varchar(50) NOT NULL DEFAULT '',
  Alias varchar(50) DEFAULT NULL,
  PRIMARY KEY (Name)
)
ENGINE = MYISAM,
CHARACTER SET utf8,
CHECKSUM = 0,
COLLATE utf8_general_ci;

ALTER TABLE groups
ADD UNIQUE INDEX Alias (Alias);


CREATE TRIGGER trg_Ins BEFORE INSERT ON groups FOR EACH ROW
  BEGIN
      DECLARE err_msg VARCHAR(128) DEFAULT NULL; 
      
      IF EXISTS(SELECT x.alias FROM groups x WHERE x.alias = NEW.name) OR (NEW.Name=New.Alias) THEN
          SET err_msg = CONCAT_WS('','cannot insert Name value ',NEW.Name,'; already exists as a Alias');
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
      END IF;

      IF EXISTS(SELECT x.Name FROM groups x WHERE x.Name = NEW.Alias) OR (NEW.Alias=NEW.Name)  THEN
          SET err_msg = CONCAT_WS('','cannot insert Alias value ',NEW.Alias,'; already exists as a Name');
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
      END IF;
  END;

CREATE TRIGGER trg_Upd BEFORE UPDATE ON groups FOR EACH ROW
  BEGIN
      DECLARE err_msg VARCHAR(128) DEFAULT NULL; 
      
      IF EXISTS(SELECT x.alias FROM groups x WHERE x.alias = NEW.name) OR (NEW.Name=New.Alias) THEN
          SET err_msg = CONCAT_WS('','cannot Update Name value ',NEW.Name,'; already exists as a Alias');
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
      END IF;

      IF EXISTS(SELECT x.Name FROM groups x WHERE x.Name = NEW.Alias) OR (NEW.Alias=NEW.Name)  THEN
          SET err_msg = CONCAT_WS('','cannot Update Alias value ',NEW.Alias,'; already exists as a Name');
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
      END IF;
  END;

-- TEST INSERT

Insert INTO Groups VALUES ('A','B');  -- OK

Insert INTO Groups VALUES ('B',NULL); -- cannot insert Name value B; already exists as a Alias 

Insert INTO Groups VALUES ('C','A'); --  cannot insert Alias value A; already exists as a Name

Insert INTO Groups VALUES ('C','D'); -- OK

Insert INTO Groups VALUES ('E','Z'); -- OK

Insert INTO Groups VALUES ('K','K'); -- cannot insert Name value K; already exists as a Alias

-- TEST UPDATE
UPDATE groups SET `Name`='A' WHERE `Name`='C'; -- Duplicate entry 'A' for key

UPDATE groups set `Name` ='X' WHERE `Name`='E'; -- OK

UPDATE groups set `Alias` ='A' WHERE `Name`='X'; -- cannot Update Name value B; already exists as a Alias

-- 将尝试更多测试