Search code examples
javasqlhibernatemariadbmariasql

Substitution for too large composite key

I'm trying to find the best solution for the given problem:

I have an Entity (lets call it Collateral) which consists of several fields. The uniqueness of this entity is defined by the composition of 4 fields (let's call them: user_id (bigint), device_id(varcha(9)), key_id(varchar(2732)), application_id(varchar(255)))

This table is being generated with hibernate. I have tried both moving this 4 fields to a separate entity (CollateralEmbeddedEntity ) to use it as embedded Id, and creating the constraint on those 4 fields within Collateral Entity:

@Table(
    name="COLLATERAL",
    uniqueConstraints=
    @UniqueConstraint(name = "comp_key", columnNames={"device_id", "application_id", "key_id", "user_id"}))

Problem is that in both cases, the fields altogether exceed the maximum allowed length of the MariaDB key:

java.sql.SQLException: Specified key was too long; max key length is 3072 bytes

Changing the dbCharset encoding (collation), or shrinking the fields varchar range itself is not an option.

What I have thought about is to generate and store a hash of those 4 fields and give it a unique constraint (search and update will always be based on these 4 fields all together anyway) however, I'm not sure if such solution is appropriate since we are violating the database normalization with redundant information.

Is the solution with hash is actually a good one? If not, what are the better alternatives for the given problem?

Solution

  • Normalize the certificate key:

    CREATE TABLE CertKeys (
    cert_id INT UNSIGNED AUTO_INCREMENT,
    cert_key VARCHAR(2732) NOT NULL,   -- base64 encoded
    -- or:  cert_key VARBINARY(2049) NOT NULL,   -- binary
    PRIMARY KEY (cert_id),
    UNIQUEY (cert_key) ) ENGINE=InnoDB;

    Then use cert_id in the other table and in the composite INDEX you are talking about.

    It takes an extra step to insert the cert_key in the new table and get the cert_id. This is done before inserting in the main table.

    It's less critical, but you might also consider normalizing application_id.

    (Yes, a different technique could be devised using a hash, but I think this is cleaner.)