Installation: SQL error, Duplicate PRIMARY key

For reference: http://api.drupal.org/api/drupal/includes%21database%21schema.inc/group/...

The cause is on line 92 of eck.install in eck_schema().

$schema = array(
  ....,
  'primary key' => array(
    'id', 'name'
  )
);

Inline comments discovered in eck.install:

/* Really the identifier should be some sort of combination between'
 * the entity type the bundle belongs too, and the bundles name
 * but since I don't know db stuff to that extent, i will leave this
 * useless id hanging around , well it seems liek CTools exportable
 * do expect some sort of id beyond the machina_name, so we will
 * leave here and added back to the types, just in case that we
 * use CTools exportables
 */

'primary key' => array('id'/*, 'machine_name'*/), //sad.. taken this out until I figure out how to make it work

Reading the inline comments in-code, I'd suggest the following:

• Your database schema is trying to mix a serial primary key identifier with a unique text key (machine name). Don't do this. Choose one path or the other.
• Your database schema is trying to mix a serial primary key with a foriegn key identifer (eck_bundle -> eck_entity_type). Don't do this. If you need a compound key, use a UNIQUE() index, and have either A) no primary key, or B) a single serial primary key.

Patch with recommendations to follow.

Here's my patch:

• It eliminates the use of the 'primary key' schema key. If you're using a 'serial' field, you don't need it/can't have it.
• It introduces UNIQUE indexes on both the "eck_entity_type", and "eck_bundle" tables.
  • eck_entity_type's unique index is only on the entity's machine_name.
  • eck_bundle's unique index is against the entity_type and the bundle's machine name.
    This means we'll ALLOW two bundles to have the same machine name, BUT
    ONLY if these bundles are attached to DIFFERENT entities.

EDIT: I didn't change the fact that you are using a 'serial' ID for each, because I don't know where in the ECK module it would affect access/behavior.

EDIT:

Retested --dev (without the aforementioned patch) on 7-16-2012@23:31PM GMT against MySQL:
Result: Installation ok.

Retested against SQLite --dev (without the aforementioned patch): Failed.
Result:
PDOException: SQLSTATE[HY000]: General error: 1 table "eck_entity_type" has more than one primary key: CREATE TABLE {eck_entity_type} ( id INTEGER PRIMARY KEY AUTOINCREMENT CHECK (id>= 0), name VARCHAR(128) NOT NULL, label VARCHAR(128) NOT NULL, properties TEXT NOT NULL, PRIMARY KEY (name) ); ; Array ( ) in db_create_table() (line 2688 of /includes/database/database.inc).

Hmm, apparently SQLite complains in my patch, not when the module is installed, but when you try to create new entities.

Notice: Undefined index: primary key in DBObject->__construct() (line 34 of /sites/all/modules/eck/eck.classes.inc).