[development] Primary keys: some are wrong, others are missing

[David Strauss]

I'd like to add that some tables have redundant indexes. If MySQL has a
key covering (col_a, col_b), it functions perfectly well for queries
using col_a only. A special col_a index is unnecessary.

This problem occurs in about three to five places, at first glance.

Barry Jaspan wrote:
> Currently, Drupal defines primary keys for some of its tables but in
> practice does not really use or rely on them as primary keys.  The
> Schema module, however, is going to use them.  I will soon begin
> development of Migrate, a module for incremental data migration
> between Drupal systems, and only tables with primary keys will be able
> to participate.
> 
> To that end:
> 
> 1.  node's primary key is wrong.
> 
> Currently, the primary key of the node table is (nid, vid), and we
> have a unique index on (vid).  I think this is incorrect.
> Semantically, if a node has nid 12 and vid 30, we do not think of it
> as "node 12/30", we think of it as "node 12."  The path to access it
> is node/12, not node/12/30.  More precisely, the nid is what uniquely
> identifies the node as an entity; therefore, it needs to be the
> primary key.
> 
> So, we can change the primary key to be (nid), add a unique index on
> (nid,vid), and leave the unique index on (vid) too.  There will be no
> performance consequences or code changes outside system.install.
> 
> 2.  Other tables have no primary key.
> 
> The tables block, filters, flood, permission, and term_relation have
> no primary key.  They should, and the Schema module will need them to
> if they are to participate in upcoming functionality.
> 
> Note that we do not have to change the code to *use* these primary
> keys, we just need to add them.  Since we have now resolved that using
> auto-incrementing fields is allowed in Drupal, we do not even need to
> change module code to maintain the primary key.
> 
> These two changes will require a core schema update with patches in
> system.install.  If there is consensus, I'll submit a patch.
> 
> Thanks,
> 
> Barry
> 
> 
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 186 bytes
Desc: OpenPGP digital signature
Url : http://lists.drupal.org/pipermail/development/attachments/20070501/0b6befcf/attachment.pgp