Gossamer Forum
Quote Reply
creator->unique error
Alex,

It seems the ->unique key is causing the following error, note the key 'term' is entered both for Index and Unique, and generates an error:

Unable to install plugin: 'Could not create table SearchLog: Failed to execute query: ' CREATE TABLE lsql_SearchLog ( ID INT unsigned NOT NULL AUTO_INCREMENT, Term CHAR(25) NOT NULL, HitCount SMALLINT unsigned DEFAULT '0' NOT NULL, Results SMALLINT unsigned DEFAULT '0' NOT NULL, Last_Hit DATE, INDEX Term (Term), UNIQUE Term (Term), PRIMARY KEY (ID) ) ' Reason: Duplicate key name 'Term')'

The original code is:

Code:



$c->cols (
ID => { pos => 1, type => 'INT', not_null => 1, unsigned => 1, regex => '^\d+$' },
Term => { pos => 2, type => 'CHAR', size => '25', not_null => 1 },
HitCount => { pos => 3, type => 'SMALLINT', unsigned => 1, not_null => 1, default => 0 },
Results => { pos => 4, type => 'SMALLINT', unsigned => 1, not_null => 1, default => 0 },
Last_Hit => { pos => 5, type => 'DATE' }
);
$c->pk('ID');
$c->ai('ID');
$c->unique(
{
Term => [ 'Term' ]
}
);
if (! $c->create()) {
$GT::SQL::errcode ||= ''; #silence -w, ugh.
$GT::SQL::errcode eq 'TBLEXISTS' ? ($message = "Could not create table SearchLog (table already exists)\n") :
($error = "Could not create table SearchLog: $GT::SQL::error)");
$c->set_defaults();
$c->save_schema();
};



BTW: it works if the index_name is changed to Term2, but you end up with TWO separate indexes --- 'Term' which is a key, and 'Term2' which is a UNIQUE key.

The same logic works differently with a compound unique key:

Code:


$c2->unique (
{
'Term_Date' => ['Term','Hit_Date']
}
);


Creates

Keys: Key name Unique Field Action HitCount NO HitCount Drop Last_Hit NO Last_Hit Drop Term_Date YES Term Drop Term_Date YES Hit_Date Drop PRIMARY YES ID Drop

Which seems to be the correct interpretation.

The table definition is listed as:

CREATE TABLE lsql_SearchLogDaily (
ID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
Term char(25) DEFAULT '' NOT NULL ,
HitCount smallint(5) unsigned DEFAULT '0' NOT NULL ,
Results smallint(5) unsigned DEFAULT '0' NOT NULL ,
Last_Hit date DEFAULT '0000-00-00' NOT NULL ,
Hit_Date date DEFAULT '0000-00-00' NOT NULL ,
KEY HitCount (HitCount),
KEY Last_Hit (Last_Hit),
UNIQUE Term_Date (Term,Hit_Date),
PRIMARY KEY (ID)
);


Which shows a unique compound key of Term_Date.

The table definition for the other table is shown as:

CREATE TABLE lsql_SearchLog (
ID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
Term char(25) DEFAULT '' NOT NULL ,
HitCount smallint(5) unsigned DEFAULT '0' NOT NULL ,
Results smallint(5) unsigned DEFAULT '0' NOT NULL ,
Last_Hit date ,
KEY Term (Term),
UNIQUE Term2 (Term),
PRIMARY KEY (ID)
);


Which is not correct. Term should have been UNIQUE, on it's own.




PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Last edited by:

pugdog: Dec 27, 2001, 11:51 PM