[BioSQL-l] Tables without a (composite) primary key

[Hilmar Lapp]

Hi Peter,

it's a known enhancement request. I know that some ORMs have trouble  
reverse engineering the mapping if there is no PK defined.

Semantically, however, in the absence of a primary key constraint the  
first unique key constraint is equivalent to a primary key (in fact  
some ER modeling tools will automatically do the conversion); unique  
keys are also called alternate keys (alternate to the primary key).

So for now feel free to either change the UK constraint to PK where  
there is no PK defined and your reverse engineering tool needs it. If  
you don't use a reverse engineering tool, just set the columns of the  
UK constraint as the compound primary key if there isn't a surrogate PK.

BioSQL 1.1+ will have surrogate PKs on all tables, but this change may  
not be backwards compatible for existing language bindings, which is  
why I'd like to make those changes first that should be fully  
backwards compatible.

	-hilmar

On Nov 6, 2008, at 6:53 AM, Peter wrote:

> I've recently been looking into some object-relational mappers which
> caused me to look more closely at the BioSQL schema.  Many of these
> packages require a primary key, but not all can cope with a composite
> primary key.  However, some BioSQL tables don't have any primary key
> at all.
>
> Several BioSQL tables have composite primary keys, for example the
> term_dbxref table has a composite key of (term_id, dbxref_id), and
> also an index on dbxref_id as well.
>
> However, some BioSQL tables do not have a primary key, for example:
>
> -- corresponds to the names table of the NCBI taxonomy databaase
> 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;
>
> CREATE INDEX taxnametaxonid ON taxon_name(taxon_id);
> CREATE INDEX taxnamename    ON taxon_name(name);
>
> Why don't taxon_name, bioentry_path, term_relationship,
> bioentry_qualifier_value, seqfeature_path have a primary key (just a
> uniqueness criteria)?
>
> Thanks,
>
> Peter
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/biosql-l

-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================