如何在 MySQL 中的這個連結表上放置這個特定的約束？

我在 MySQL 中有以下三個表：Store、Groups、StoreGroup。一個商店可以屬於多個組。StoreGroup 是連結表，記錄了一個商店所屬的組。該Groups表具有以下欄位：

+------------+----------------------------------+------+-----+---------+----------------+
| Field      | Type                             | Null | Key | Default | Extra          |
+------------+----------------------------------+------+-----+---------+----------------+
| id         | int(11)                          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)                     | YES  | UNI | NULL    |                |
| category   | enum('Area','Level','Temp')      | YES  |     | NULL    |                |
| expireDate | datetime                         | YES  |     | NULL    |                |
+------------+----------------------------------+------+-----+---------+----------------+

記錄各個店鋪分組的連結表StoreGroup有以下欄位：

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| storeID | varchar(16) | NO   | PRI | NULL    |       |
| groupID | int(11)     | NO   | PRI | 0       |       |
+---------+-------------+------+-----+---------+-------+

然而，即使一家商店可以屬於多個組，它也有一個約束，即它只能屬於一個組類別中的一個組。例如，具有storeIDABC 的商店可以同時屬於 groupID 為 1001 和 1002 的兩個組，因為 1001 來自'Area'類別，而 1002 來自'Level'類別。但它不能分配給 groupID 1003，因為組 1001 和 1003 都來自同一個組類別'Area'，如下所示：

SELECT * FROM Groups;

+------+--------------+----------+---------------------+
| id   | name         | category | expireDate          |
+------+--------------+----------+---------------------+
| 1001 | City A       | Area     | NULL                |
| 1002 | Branch       | Level    | NULL                |
| 1003 | City B       | Area     | NULL                |
+------+--------------+----------+---------------------+

因此，當想要將記錄插入 StoreGroup 表時，以下是可以的：

INSERT INTO StoreGroup VALUES('ABC', 1001);
INSERT INTO StoreGroup VALUES('ABC', 1002);

但是以下應該失敗，因為 storeID'ABC'不能同時屬於 1001 和 1003 組，因為 1001 和 1003 都來自類別'Area'：

INSERT INTO StoreGroup VALUES('ABC', 1003);    --This should fail.

那麼如何修改表StoreGroup或Groups添加此約束，以便將商店分配給具有相同類別的組會失敗？

解決方案是在表中添加組類別列，StoreGroups然後在(storeId, groupCategory). 表定義如下：

CREATE TABLE Groups
 ( groupID INT NOT NULL PRIMARY KEY
 , name VARCHAR(255) NOT NULL
 , groupCategory NOT NULL VARCHAR(30)
 , exprireDate DATE NULL
 , UNIQUE (groupCategory, groupID)       -- this is needed for the FK below
 ) ;

CREATE TABLE StoreGroups
 ( storeID VARCHAR(16) NOT NULL 
 , groupID INT NOT NULL PRIMARY KEY
 , groupCategory NOT NULL VARCHAR(30)
 , PRIMARY KEY (storeId, groupID)
 , FOREIGN KEY (groupCategory, groupID)
     REFERENCES Groups (groupCategory, groupID)
 , UNIQUE (storeID, groupCategory)              -- your rule enforced
 ) ;

該UNIQUE約束強制規定不能有 2 個StoreGroups具有相同storeID和相同的groupCategory。

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