perlquestion
chrestomanci
DBIx::Class unique constraint with limited key length
<p>Greetings wise brothers. I seek your wisdom on how to be confident that all are different without the need to remember every part.</p>
<p>I am working [mod://DBIx::Class], and I would like to add a unique constraint of limited key length to a much longer varchar column</p>
<p>My DBIC schema definition code looks like this:</p>
<c>
__PACKAGE__->table("classification_rules");
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
...
"path",
{ data_type => "varchar", is_nullable => 0, size => 600 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint( ['path'] );
</c>
<p>This generates MySQL like this:</p>
<c>
CREATE TABLE `classification_rules` (
`id` integer NOT NULL auto_increment,
...
`path` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE `classification_rules_path` (`path`)
) ENGINE=InnoDB;
</c>
<p>The problem is, that when I deploy, MySQL correctly reports that you can not have a unique constraint on a varchar that long without limiting the key length. What I would like to do, is have it generate SQL like <c>UNIQUE `classification_rules_path` (path(250))</c> but I cannot work out how.</p>
<p>I have already stepped through the add_unique_constraint code in [mod://DBIx::Class::ResultSource] to try to find an undocumented feature or hook I can use and also added breakpoints in [mod://SQL::Translator::Producer::MySQL] to try and spot where the SQL is being generated from the other end.</p>
<p>Is there another solution to this? Is there a hook to add literal SQL to the definition for my table?</p>
<p>Thank you.</p>