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

Issue Type: Improvement Created: 2006-06-19T23:05:05.000+0000 Last Updated: 2007-07-05T14:43:07.000+0000 Status: Resolved Fix version(s): - 0.9.3 (05/May/07)

Reporter: Zend Framework (zend_framework) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db_Table

Related issues: - ZF-1064



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.


Posted by Gavin (gavin) on 2006-07-06T17:07:29.000+0000

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

Posted by Shannon Wynter (freman) on 2006-09-06T05:14:24.000+0000

If by Multiple Primary Keys you mean the following example


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

Posted by cykit luo (cykit) on 2006-11-02T06:25:38.000+0000

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,

Posted by Stefan Pettersson (stefpet) on 2006-11-06T15:09:26.000+0000

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.

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:22:36.000+0000

Changing fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2006-12-26T18:43:58.000+0000

Rephrase summary.

Posted by Bill Karwin (bkarwin) on 2006-12-29T15:11:26.000+0000

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.

Posted by Bill Karwin (bkarwin) on 2007-01-05T17:06:26.000+0000

Recategorize as Zend_Db_Table component.

Posted by Bill Karwin (bkarwin) on 2007-03-15T22:14:06.000+0000

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.

Posted by Bill Karwin (bkarwin) on 2007-05-01T13:32:02.000+0000

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

Posted by Bill Karwin (bkarwin) on 2007-05-03T17:24:30.000+0000

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

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.