跨两列的MySQL唯一约束

Question

我有一个定义关系的表格

Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields

然后，我需要添加一个约束，表示给定值是否存在于其中一列中。

1)不能在同一栏中复制。

2)也不能在另一栏中复制。

这是无效的

src    dst
1      354
666    1

因为值1存在于第一行中，所以它不能出现在第二行中。

如何定义这种类型的约束？

我在应用杠杆上做一个轻量级的检查。但我希望数据库能确保。

更新:目前我有7种不同类型的关系，每种关系类型有一个表。

更新2:最初，这只是一个包含所有关系的表，现在我正在开发它

# variante
Create TABLE `productsRelationships3` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-3` (relSrc, relDst),
  UNIQUE  `src-3` (relSrc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# this is the import
INSERT INTO productsRelationships3 SELECT relSrc, relDst FROM  productsRelationships WHERE relType=3;
DELETE FROM productsRelationships WHERE relType=3;

#this is the retrieval. The dummy rows are there because I do a UNION
#SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, 28281 as source FROM productsRelationships3 LEFT JOIN productsRelationshipsDesc on 3=relTypeID WHERE relDst=28281 OR relSrc=28281;



# fraccion
#relType is from the old 1-table schema. It's going to be deleted
Create TABLE `productsRelationships6` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relType` tinyint(2) unsigned NOT NULL DEFAULT 6,
  `fracQty` int(2) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-6` (relSrc, relDst),
  UNIQUE  `src-6` (relSrc),
  UNIQUE  `dst-6` (relDst),
  CONSTRAINT `fk_type_desc_6` FOREIGN KEY (`relType`) REFERENCES `productsrelationshipsdesc` (`relTypeID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#import
INSERT INTO productsRelationships6 SELECT relSrc, relType, fracQty, relDst FROM  productsRelationships WHERE relType=6;

其他表基本上与productsRelationships3相同

Answer 1

我对Y立方体溶液做了一点修改，使源和目的地都不是空的，这是原始设计所保证的。我的CHECK约束被注释掉了，因为它们显然不能在MySQL中工作。我保留了它们作为注释，因为它们记录了我的意图，并且它们将在其他RDBMS上工作。

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType 
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'), 
  (2, 'Destination') ;

CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  PointID smallint(5) unsigned NOT NULL,
  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  SourcePointID smallint(5) unsigned NOT NULL,
  SourceTypeID tinyint unsigned NOT NULL, 
      -- CHECK(SourceTypeID = 1),
  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  DestinationPointID smallint(5) unsigned NOT NULL,
  DestinationTypeID tinyint unsigned NOT NULL, 
      -- CHECK(DestinationTypeID = 2),
  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  --  other fields
  PRIMARY KEY (RouteID)
) ;

测试：

insert into PointUsageQuota
values(1,1,666),(1,2,354);

INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);

Answer 2

我认为在当前的设计中不可能有这种限制。如果您可以更改它，并假设表现在是：

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  Src smallint(5) unsigned NOT NULL,
  Dst smallint(5) unsigned NOT NULL,
  -- other fields
  PRIMARY KEY (RouteID)
) ;

您可以拆分成两个表，并将这两个列移动到新表中，并将它们组合成一个列(SrcDst)。一个只有2行的小型引用表(PointType)将有助于强制执行这样的要求，即您有一个Src和一个Dst，而且没有更多：

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  --- other fields
  PRIMARY KEY (RouteID)
) ;

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType 
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'), 
  (2, 'Destination') ;

CREATE TABLE RoutePoint
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  SrcDst smallint(5) unsigned NOT NULL,
  PRIMARY KEY (RouteID, PointTypeID),
  UNIQUE (SrcDst),                       -- and this is what all the fuss is about
  FOREIGN KEY (RouteID)
    REFERENCES Route (RouteID)
      ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

这意味着旧Route表上的任何行现在都将是新Route中的1行和RoutePoint表中的2行。

这意味着现在不能简单地将INSERT放到Route表中。您必须使用一个事务，以确保如果在Route表中插入一行，也会在RoutePoint表中插入2行。否则，您可能在Route中有没有Src或Dst的行。

必须对两个表的UPDATE和DELETE语句进行类似的更改，因此不会意外地从RoutePoint表中更改或删除行(例如，在Route中留下一行而没有相关的Src或Dst数据)。

Answer 3

这可以通过触发器来实现。

参见此SQLFiddle用于工作模型。将第二个INSERT更改为一个重复的值，您将看到插入失败并有错误消息。使用致命信号从“插入前”触发器中跳转可以防止插入的实际发生。

CREATE TRIGGER Route_bi BEFORE INSERT ON Route FOR EACH ROW
  BEGIN
      DECLARE err_msg VARCHAR(128) DEFAULT NULL; 

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

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

Src和Dst列都需要一个唯一的键，这将防止同一列中的重复值。

您还必须在更新触发器之前处理类似的条件，以避免以后将值更改为重复值。