MySQL – How to Get Same Value in Unique Column Differentiated by Other Column Values

indexMySQL

I have a table like this

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1

PRODUCT_NAME must be unique for same manufacturer and same package ID, BUT it can be duplicate if manufacturer ID or Product ID are different.
I tried using UNIQUE but it is rejecting same values with different Manufacturer ID or Package ID

My desired output would be like :

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1
  3 |   VITAMIN A  |       2         |      1

Notice third value of PRODUCT_NAME 'VITAMIN A' is same but MANUFACTURER_ID is different.
Do we have any work-around for this other than UNIQUE?

Best Answer

I think the problem here is that you were unaware that UNIQUE constraints can contain more than one field. Based on your question, I suggested 2 fields, i.e. both product and manufacturer.

It appears now (following comments) that 3 are required, as originally suggested by @ypercube.

From the link I originally posted here, the syntax you require is as follows:

CREATE UNIQUE INDEX 'my_product_uq'  
ON my_product (product_name, manufacturer_id , package_id);

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

Here is a sample loading into this table:

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

Let's look at the the last WorkorderNum from each site

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

Let's run this and see what happens

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.

MySQL-How to find that value in a column is subset with all other values in same column multiple time

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic.

You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're at it you could throw in the third table to store the Tasks that the Team Groups are bound to (trusting they are bound to be changed from time to time and you'd like to know that history).

The second option is to write a script that does pretty much what you'd be doing in the first option except it reflects the change back to the table in the de-normalized format.

This isn't trivial. You'll need to loop over each row, split the column into the atomic team names and then query the table using a FIND_IN_SET() for each team name plus ensuring not to match the row your cursor is pointing to. Every time the table changes you'll need to re-run the query.

Here is some SQL that demonstrates normalizing the data. It has foreign keys for data consistency. The task IDs are not stored in the team_group table in this example instead a separate table exists which allows for multiple tasks.



CREATE TABLE `team` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL COMMENT 'Name of Team',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `team_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '' COMMENT 'Optional Name of Team Group',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a team group can be one or more teams';

CREATE TABLE `team_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_team_group` (`team_id`,`group_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `team_group_map_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds teams to a team group';

CREATE TABLE `task` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `task_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` mediumint(8) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_group_task` (`group_id`,`task_id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `task_group_map_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds tasks to a team group';


-- Add a new team
INSERT INTO team (name) VALUES ('Team1');

-- Add a new group and add an existing team to it
INSERT INTO team_group (name) VALUES ('Euro east group');
-- Get ID for this group
SELECT id FROM team_group WHERE name = 'Euro east group';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add team to the group (team_id = 1, group_id = 1)
INSERT INTO team_group_map (team_id, group_id) VALUES (1, 1);

-- Add a task and set it for a group
INSERT INTO task (name) VALUES ('Win Finals');
-- Get ID for this task
SELECT id FROM task WHERE name = 'Win Finals';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add task to the group (task_id = 1, group_id = 1)
INSERT INTO task_group_map (task_id, group_id) VALUES (1, 1);

-- Select the teams and group IDs
SELECT team.name, group_id
FROM team_group_map
INNER JOIN team ON (team.id = team_id);

-- Select the teams and group names
SELECT team.name, group.name 
FROM team_group_map
INNER JOIN team ON (team.id = team_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups, their task IDs and when the tasks were added to the group
SELECT group.name, task_id, map.updated_at
FROM task_group_map AS map
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups and their task Names + when tasks were added to group
SELECT group.name, task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the most recent task for a group
SELECT task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
WHERE group_id = 1
ORDER BY map.updated_at DESC
LIMIT 1;

Best Answer

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

MySQL-How to find that value in a column is subset with all other values in same column multiple time

Related Question