MySql Primary Key > 900/1000 bytes?

Question

I have a composite primary key, which together turns out to be rather large (~2000 bytes). I have no performance considerations, I just would like a primary key to enforce uniqueness.

MySql doesn't like long primary keys. Is there a way around this? Perhaps to only enforce uniqueness, without building an index?

I wouldn't want to use ASCII instead of UTF8 just to enable a primary key (UTF8 character takes 3 bytes).

My table is defined as follows:

CREATE TABLE `configuration` (
  `Section` varchar(200) NOT NULL,
  `StoredKey` VARCHAR(200) NOT NULL,
  `ServiceName` VARCHAR(300) NOT NULL,
  `ServiceMajorVersion` int unsigned NOT NULL,
  `ServiceMinorVersion` int unsigned NOT NULL,
  `ServiceInstanceID` VARCHAR(100) NOT NULL,
  `StoredValue` VARCHAR(1024)

 , PRIMARY KEY (`Section`, `StoredKey`, `ServiceName`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

Use autoincremented integer for primary key and add another unique key.

Alternatively you could try using binary(16) as primary key with unhex(md5(concat(columns))) as value.

Answer 2

It's hard to say without understanding your database, but it might need some normalization. You could always make a UNIQUE INDEX that is not a primary key, and if there is already a single column which will be unique, make that a primary key, and if there is not, you can make a surrogate primary key (INTEGER AUTO_INCREMENT).

Answer 3

Agree with what @pornel and @CadeRoux already posted; you would be better off creating a surrogate primary key (ConfigurationId) as an autoincremented integer column, an dthen create separate unique index.

Answer 4

You should read some book about DB structure design, then you will not have tables with such primary keys. Or hire someone who my create (prototype) for DB structure for you. This is just friendly advice.

Answer 5

@porneL has the correct answer for this case, however, @Cade Roux and @noonex are also correct: Databases are not meant to be used like Excel.

You should have secondary tables:

CREATE TABLE ServiceInstance (
    ID int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Hash binary(16) NOT NULL,
    ServiceInstanceID varchar(100) NOT NULL,
    UNIQUE(Hash)
);

For each table except data which will be unique per configuration line.

When you insert, do:

INSERT INTO ServiceInstance (Hash, ServiceInstanceID) VALUES (unhex(md5('whatever')), 'whatever');

Then, your primary table becomes:

CREATE TABLE `configuration` (
    `Section_ID`          int unsigned NOT NULL,
    `StoredKey_ID`        int unsigned NOT NULL,
    `ServiceName_ID`      int unsigned NOT NULL,
    `ServiceMajorVersion` int unsigned NOT NULL,
    `ServiceMinorVersion` int unsigned NOT NULL,
    `ServiceInstanceID`   int unsigned NOT NULL,
    `StoredValue`         VARCHAR(1024),
    UNIQUE (`Section_ID`, `StoredKey_ID`, `ServiceName_ID`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceInstanceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Use the UNIQUE key instead, as PRIMARY KEYs are typically used when you will be accessing the rows by the primary key always. If it's just a constraint, use UNIQUE instead.