How to put this particular constraint on this link table in MySQL?

Question

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table has the following fields:

+------------+----------------------------------+------+-----+---------+----------------+
| 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    |                |
+------------+----------------------------------+------+-----+---------+----------------+

The link table StoreGroup that records each store's groups has the following fields:

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

However, even though a store can belong to several groups, it has a constraint that it can belong to only one group in one group category. For example, a store with storeID ABC can belong to both groups with groupID 1001 and 1002 since 1001 is from 'Area' category and 1002 is from 'Level' category. But it cannot be assigned to groupID 1003 because both group 1001 and 1003 are from the same group category 'Area' as shown below:

SELECT * FROM Groups;

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

So when one wants to insert the record into StoreGroup table, the following is OK:

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

But the following should fail because storeID 'ABC' cannot belong to both groups 1001 and 1003 since 1001 and 1003 both are from category 'Area':

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

So how to modify the table of StoreGroup or Groups to add this constraint so that assigning store to groups with same category will fail?

ypercubeᵀᴹ · Accepted Answer · 2014-08-08T21:55:54.633

The solution is to add the group category column in the StoreGroups table and then add a unique constraint on (storeId, groupCategory). The table definitions would be like this:

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

The UNIQUE constraint enforces that there cannot be 2 StoreGroups with the same storeID and the same groupCategory.

How to put this particular constraint on this link table in MySQL?

1 Answers1