0

I have a table with a unique key which is a combination of 4 columns. Column A,B,C and D.

The values in the columns are like this:

Column A Column B Column C Column D
111000 150100_XYZ_XY02 150100-A2_WXYZ_XY02 150100-A2_17090000_WXYZ_XY02B
222111 150122_XYZ_XY02 150122-A2_WXYZ_XY02 150122-A2_17090000_WXYZ_XY02B

When I insert the following record in the table,

Column A Column B Column C Column D
222111 150122_XYZ_XY02 150122-A2_WXYZ_XY02 150122-A2_17090000_WXYZ_XY02

Notice that the only difference is last character of the Column D. It throws the exception:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry '222111-150122_XYZ_XY02-150122-A2_WXYZ_XY02-150122-A2_17090000_WXYZ_XY02' for key 'my_table_unique_idx1' 

Even though there's a difference in the value of one column, why there would be an exception?

Update: Here's the schema:

 CREATE TABLE mytable
(
   my_uuid  VARCHAR(100)    NOT NULL,
   s_id                               VARCHAR(100)    NOT NULL,
   p_id                                VARCHAR(100)    NOT NULL,
   pl_id                                  VARCHAR(100)    NOT NULL,
   ac_id                               VARCHAR(100),
   m_seg                              VARCHAR(100),
   et_date                            DATETIME,
   etr_date                  DATETIME,
   refreshed_date                           TIMESTAMP,
   created_date                             TIMESTAMP       DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_date                             TIMESTAMP       DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL,
   deleted_date                             TIMESTAMP NULL DEFAULT NULL,
   PRIMARY KEY (`p_id`, `my_uuid`),
UNIQUE KEY `really_really_long_name_for_unique_idx1` (`p_id`,`s_id`,`pl_id`,`ac_id`),
  KEY `really_really_long_name_for_updated_date` (`updated_date`),
  KEY `really_really_long_name_for_seg_idx_1` (`m_seg`),
  KEY `really_really_long_name_for_et_date_idx_1` (`et_date`),
  KEY `really_really_long_name_for_etr_date_idx_1`(`etr_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!50100 PARTITION BY KEY (p_id)
PARTITIONS 100 */

Here's the fiddle - I am unable to reproduce the same in fiddle though: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=59ed46385738617c8b456f8b661a96fd

6
  • I would suggest posting the definition of my_table_unique_idx1. While you may think it is on columns A,B,C,D perhaps it is only on columns A,B,C?
    – Nick S
    Jun 30, 2022 at 15:17
  • I am preparing a fiddle and also adding the definition here in a few minutes Jun 30, 2022 at 15:33
  • @ErgestBasha I have updated the description with fiddle Jun 30, 2022 at 17:19
  • @NickSI have added the definition Jun 30, 2022 at 17:19
  • @JohnEisbrener No, I am not - Jun 30, 2022 at 17:19

1 Answer 1

1

UNIQUE indexes do not work with PARTITIONed tables.

Furthermore, having the partition key be the first column in an index is almost always not useful.

3
  • thank you for looking into this, could you please what should be the approach then? Also, could you share the documentation link supporting your answer, if that's okay? Thanks again! Jul 1, 2022 at 10:38
  • @Vishal_Kotecha - Much experimentation, summarized in blog on Partitioning. So far, I see no benefit in Partitioning; what other queries do you have? The lack of UNIQUE and FK is mentioned in the main docs, plus in multiple feature requests (aka bug reports).
    – Rick James
    Jul 1, 2022 at 14:58
  • Wow, that's a killer - good to know!
    – J. Gwinner
    Jul 18, 2023 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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