0

i have a table with primary key defined (post_id,category_id).his table implement Many_Many relation. how to add a autoincrement field to this table (i usually use auto_increment field to easily fetch records and use auto_increment value in form). when i try to add auto_increment field i get this error:

 Multiple primary key defined

is there any need to have auto_increment fields when i have composite primary key? if yes how to do this?

user006779
  • 1,011
  • 4
  • 15
  • 28

2 Answers2

1

If a table has an auto increment field, it must be the Primary Key. There can only be one Primary Key.

I would not create an auto increment just for the sake of it, since it then means you have to add a unique index on the fields which are a natural candidate for the PK.

The only time I would create an auto_increment in this case, is if the natural PK would consist of unsuitable data types (varchar etc), or the PK would be large (in terms of bytes) or spanning quite a lot of columns

carpii
  • 1,917
  • 4
  • 20
  • 24
0

post_id,category_id should be foreign keys of your join table.

it should look like this:

--------------
post_category
==============
pkId (pk) (auto increment)
post_id (fk -> post table)
category_id (fk-> category table)
--------------
Kent
  • 189,393
  • 32
  • 233
  • 301
  • in tutorial join table have a composite primary key and database ed diagram http://www.yiiframework.com/doc/guide/1.1/en/database.arr and post #3 in this link http://www.yiiframework.com/forum/index.php?/topic/12637-please-explain-how-to-do-many-to-many-relationships-for-newcomer/ – user006779 Oct 17 '11 at 19:23
  • yes. but you want to add a auto-increment PK to that table, don't you? – Kent Oct 17 '11 at 19:25
  • this is my question. autoincrement pk or composite pk?it seems if i want to use yii i should use composite pk – user006779 Oct 17 '11 at 19:28
  • there is no auto_increment FIELD. if a field is auto-incr, it must be pk. you can in your program code to maintain a auto-increment field. – Kent Oct 17 '11 at 19:30
  • do you mean i have a composite PK and a unique int field valued by code instead of autoincrement field? is there a need to this unique field when i use yii or composite pk is enough? – user006779 Oct 17 '11 at 19:37