MySQL 跨兩列的唯一約束

我有一個定義關係的表

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基本相同

我已經稍微修改了 ypercube 的解決方案，以便源和目標都不為空，正如原始設計所保證的那樣。我的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);

我認為目前的設計不可能有這種約束。如果您可以更改它並假設該表現在是：

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

您可以拆分為 2 個表並將兩列移動到新表中，將它們合併為一列 ( SrcDst)。PointType一個只有 2 行的小參考表 ( ) 將有助於強制執行您擁有 aSrc和 a的要求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表上的任何行現在將是新表中的 1 行Route和表中的 2 行RoutePoint。

這意味著現在你不能簡單地INSERT進Route表。您必須使用一個事務來確保如果在表中插入一行，則在Route表中也插入 2 行RoutePoint。否則，您可能在Route沒有Src或的情況下有行Dst。

必須對兩個表的UPDATEandDELETE語句進行類似的更改，因此不會意外更改或從RoutePoint表中刪除任何行，例如，留下Route沒有相關Src或Dst數據的行。

引用自：https://dba.stackexchange.com/questions/29336