Issues

ZF-37: Support multi-column primary keys in Zend_Db_Table

Description

http://framework.zend.com/developer/ticket/24

Zend should support multiple primary keys in the Zend_Db_Table class. An array could be used to store this information instead of the current scalar.

Comments

Use cases? Does anyone else need / want this feature?

If by Multiple Primary Keys you mean the following example

CREATE TABLE tbl ( ParentID INT NOT NULL, ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ParentID,ID) );

Then yes, I could greatly use/want/need this feature.

I think it's necessary. for example: I have 3 tables table users with columns id, name table roles with columns id, name table users_roles with columns user_id, role_id

I use users_roles to relate users with roles, and I don't want to create a primary key id Instead, I would like to use multiple primary keys PRIMARY KEY(user_id,role_id)

now, I just cann't do it like this. If I set user_id or role_id as primary key, because of primary key can not be modified, table users_roles lose its function.

e.g. I want to add a role to an user First, I fetchNew a row

class RolesUsers extends Zend_Db_Table { protected $_primary = 'user_id'; } $ru = new RolesUsers(); $lineitem = $ru->fetchNew(); $lineitem->roleId = 1; // it's ok $lineitem->userId = 1;// throw an exception $lineitem->save();

Sorry for my poor English, is my description clear? If not, send to a mail, cykit@126.com

I've need for this too.

I've got a relation called 'participant' between the objects 'meetup' and 'user'. It looks like this:

CREATE TABLE participants ( meetup_id int(10) unsigned NOT NULL default '0', user_id int(10) unsigned NOT NULL default '0', status enum('yes','maybe','no') NOT NULL default 'yes', PRIMARY KEY (meetup_id,user_id) );

There is nothing preventing multiple primary key after normalization according to 3NF, so I believe this should be supported.

Changing fix version to 0.8.0.

Rephrase summary.

Current Zend_Db_Table assumes that the primary key is called id and is a single column.

There are many use cases that break this:

  • Primary key is not called id. It is a best practice to name primary keys something unique across the database, so that foreign keys can be named the same.

  • Primary key is compound, consisting of more than one column.

  • Primary key is natural, instead of being an autogenerated key. Many people believe that every table must have a pseudokey that is autogenerated, but this is in fact the exception, not the rule. It is common and recommended to use natural keys; that is, a column or columns from the set of attributes that comprises a unique identifier for rows.

To support this, we need to store _primary as an array. All usage of it needs to be converted to array operations. This is likely to break backward compatibility.

insert() cannot return an auto-generated id if the table does not use such. It can return the primary key value(s) instead.

Recategorize as Zend_Db_Table component.

As of revision 3995, some part of the work to support compound primary keys is done. The Zend_Db_Table_Abstract class stores primary keys as an array and many operations $_primary as an array.

However, currently the insert() method of Zend_Db_Table_Abstract still assumes that the insert operation will generate a primary key value, and this value can be retrieved by using lastInsertId(). This is true only for server-side auto-generated id columns, such as AUTO_INCREMENT in MySQL. This does not work for Oracle, PostgreSQL, or DB2. It also doesn't work for MySQL or any RDBMS for tables that have compound or natural keys.

Also, the __set() method of Zend_Db_Table_Row_Abstract assumes that it is illegal to set the primary key value of a row explicity, and the save() method assumes that the only time that it should insert a row instead of update it is when the primary key column(s) are empty.

So some more work needs to be done. Leaving this issue open for now.

In revision 4643, the Zend_Db_Table_Abstract::insert() method should return an array when the table's primary key has multiple columns.

As far as I know, all use cases have been implemented as of revision 4697.