[DDC-1318] Unexpectet behavior while using ManyToOne as part ofr composite key Created: 04/Aug/11  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers
Affects Version/s: 2.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Peter Jasiulewicz Assignee: Benjamin Eberlei
Resolution: Incomplete Votes: 0
Labels: None
Environment:

ubuntu 64x


 Description   

Hi!

Ran into a problem while wanted to use a ManyToOne as part of a primary key:

 * @Entity
 * @Table(name="user_preferences")
  */
class UserPreferences  {

    /**
     * @Id
     * @ManyToOne(targetEntity="User",cascade={"persist"})
     *  @JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;
    /**
     * @Id
     * @GeneratedValue(strategy="NONE")
     * @Column(name="preference_id",type="smallint",nullable=false)
     */
    protected $preference_id;

By default doctrine creates a table with 2 single keys (the preference key is the primary key) which is of course incorrect.
Had to add @Column(name="user_id",type="integer") to the user column to fix the index problem but that introduced another problem. The entity no longer accepter \Entity\User as a value for user and takes only a smallint as defined.

 Comments   
Comment by Benjamin Eberlei [ 04/Aug/11 ]

This is probably because of the @GeneratedStrategy annotation, can you try to just remove it? For me there is always only one key generated.

Comment by Peter Jasiulewicz [ 04/Aug/11 ]

Hi,
thanks fo the fast reply

     * @Id
     * @ManyToOne(targetEntity="User",cascade={"persist"})
     *  @JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;

     * 
     * @Id
     * @Column(name="preference_id",type="smallint",nullable=false)
     */
    protected $preference_id;

On MySQL creates:

mysql> show indexes from xxx.user_preferences;
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| user_preferences |          0 | PRIMARY                      |            1 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| user_preferences |          1 | user_preferences_user_id_idx |            1 | user_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

With:

     * @Id
     * @ManyToOne(targetEntity="User",cascade={"persist"})
     *  @JoinColumn(name="user_id", referencedColumnName="id")
     * @Column(name="user_id",type="integer")
     */
    protected $user;
 
     
     * @Id
     * @GeneratedValue(strategy="NONE")
     * @Column(name="preference_id",type="smallint",nullable=false)
     */
    protected $preference_id;

Creates:

mysql> show indexes from xxx.user_preferences;
+------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| user_preferences |          0 | PRIMARY  |            1 | user_id       | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| user_preferences |          0 | PRIMARY  |            2 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

The annotations:

     * @Id
     * @ManyToOne(targetEntity="User",cascade={"persist"})
     *  @JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;

     * @Id
     * @Column(name="preference_id",type="smallint",nullable=false)
     */
    protected $preference_id;

Create the same effect like the first one:

mysql> show indexes from xxx.user_preferences;
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| user_preferences |          0 | PRIMARY                      |            1 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| user_preferences |          1 | user_preferences_user_id_idx |            1 | user_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Sequence of events :
1) change in annotations in entity object
2) dropped the table affected
3) orm:schema-tool:update --force

Comment by Benjamin Eberlei [ 06/Aug/11 ]

Formatted code.

Comment by Benjamin Eberlei [ 06/Aug/11 ]

Can you drop the table and show the DDL generated by both "orm:schema-tool:create --dump-sql" and "orm:schema-tool:update --dump-sql" ?

I checked with our testsuite and all the entities generate the correct primary keys.

Comment by Marco Pivetta [ 23/Jan/13 ]

Is this still valid with newer versions of the ORM?

Comment by Benjamin Eberlei [ 01/May/13 ]

No feedback given

Generated at Mon May 20 19:14:14 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.