[BioSQL-l] Python ORM mapping for BioSQL

[Peter]

On Wed, Nov 26, 2008 at 7:28 PM, Hilmar Lapp <hlapp at gmx.net> wrote:
> On Nov 26, 2008, at 1:37 PM, Peter wrote:
>> Yes, SQLAlchemy seems pretty good.  The only catch was for a table
>> with no primary key defined at all (the taxon_name table) which
>> required a little more work setting up the ORM mapping, but which also
>> seems to work fine.
>
> It has one unique key defined on (name, name_class, taxon_id). Is that not
> what you are seeing?
>
>        -hilmar

According to the MySQL schema, taxon_name has a unique restraint but
does NOT have a primary key:

CREATE TABLE taxon_name (
       taxon_id		INT(10) UNSIGNED NOT NULL,
       name		VARCHAR(255) BINARY NOT NULL,
       name_class	VARCHAR(32) BINARY NOT NULL,
       UNIQUE (taxon_id,name,name_class)
) TYPE=INNODB;

As you said, since (taxon_id,name,name_class) is unique, this tuple
can be used as a substitute primary key in the ORM mapping (which for
SQLAlchemy I seem to have to do manually).  SQLAlchemy would do this
automatically if the schema actually used (taxon_id,name,name_class)
as a primary key explicitly.  i.e. Why not this:

CREATE TABLE taxon_name (
       taxon_id		INT(10) UNSIGNED NOT NULL,
       name		VARCHAR(255) BINARY NOT NULL,
       name_class	VARCHAR(32) BINARY NOT NULL,
       PRIMARY KEY (taxon_id,name,name_class)
) TYPE=INNODB;

See also this thread where I wrote:
http://lists.open-bio.org/pipermail/biosql-l/2008-November/001386.html
> Was there a reason why tables like taxon_name never had a
> (composite/compound) primary key in the first place?

Thanks,

Peter