Problem saving models with composite primary key

Hello my friends!

It is very simple to reproduce my problem, but I just don’t get it why is it so.

I have a table with composite primary key.

Here is a code snippet from db migration:




$this->createTable('{{type_category}}', array(

			'type_id' => 'INT NOT NULL',

			'category_id' => 'INT NOT NULL',

			'PRIMARY KEY (type_id, category_id)'

		));

		

$this->addForeignKey('pk_type_category_type_id', '{{type_category}}', 'type_id', '{{type}}', 'id');

$this->addForeignKey('pk_type_category_category_id', '{{type_category}}', 'category_id', '{{category}}', 'id');

I also created a model for this table and added primaryKey method there:




public function primaryKey()

{

	return array('type_id', 'category_id');

}



Table ‘type_category’ handles many-to-many relation for 2 other tables (table ‘type’ and table ‘category’)

I want to save data to this table manually (without any many-to-many advanced behaviors).

My question is…

When I do it like this:




$arTypeCategory->type_id='1'; // I use here the ID from table 'type', the record with this ID does exists

$arTypeCategory->category_id='2'; // I use here the ID from table 'category', the record with this ID does exists

$arTypeCategory->save();



I see that category_id stays null and save() doesn’t work (I get no error message, but type_category is empty)

But when I use the following code, it seems to work pretty fine:




$arTypeCategory->primaryKey = array('type_id' => $typeId, 'category_id' => $arCategory->id);

$arTypeCategory->save();



Is it bug or feature or am I doing something wrong?

You need to create an index for each column you want to be a foreign key before adding them. :)

Just call the indices the same name as the name you’re going to use for the foreign keys.

As far as I understand, primary key is automatically indexed.

I’ll add indexes for each separate column, that could improve performace, thank you.

But I don’t think, that indexes cause the described problem.

When I do the following, why do I get $arTypeCategory->category_id equals null after that???




$arTypeCategory->type_id='1'; // I use here the ID from table 'type', the record with this ID does exists

$arTypeCategory->category_id='2'; // I use here the ID from table 'category', the record with this ID does exists



I am not referring to primary but foreign keys.

Check your model rules. :)

Don’t know why, but adding indexes helped %-)

Thanks a lot, jacmoe!

Check this:

http://dev.mysql.com…onstraints.html :)