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?
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.)