1

This question already has an answer here:

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?

marked as duplicate by ypercubeᵀᴹ, Paul White, RolandoMySQLDBA mysql Aug 10 '14 at 0:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

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.

  • Thanks. However, the category field in Groups table is ENUM type which takes three possible values: AREA, LEVEL, TEMP. It is not VARCHAR type as in your code. So how to impose this constraint on ENUM typed category field? Is it the same as VARCHAR type? – tonga Aug 8 '14 at 21:57
  • Just change the 2 columns (in Groups and in StoreGroups) from varchar(30) to enun(...). Both columns have to be the same datatype for the FK to be defined. So if you have enum, make the other the same enum as well. – ypercubeᵀᴹ Aug 8 '14 at 21:59
  • 1
    I don't really like enum by the way. For one, think about how many work you'll have to do if you ever need to add 1 more category. Changing the datatypes of some columns in several tables is not trival at all. – ypercubeᵀᴹ Aug 8 '14 at 22:04
  • I did ALTER TABLE by adding all additional constraints and it works. Thanks a lot. However, this solution has added one more column (groupCategory) in the link table StoreGroups. This adds some redundant information in this table since groupCategory is already in the Groups table. And I need to add this additional column into StoreGroups table based on the existing Groups table in order to achieve that. Is this the usual way to achieve this? – tonga Aug 9 '14 at 2:01
  • Yes, this is a common solution, if you want to enforce this rule. – ypercubeᵀᴹ Aug 9 '14 at 17:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.