primary key in junction table

Yii 2.0 General Discussions
1

Reading migration about add junction table and I want to known the purpose of COMPOSITE PRIMARY KEY in a junction table




class m150811_220037_create_junction_post_and_tag extends Migration

{

    public function up()

    {

        $this->createTable('post_tag', [

            'post_id' => $this->integer(),

            'tag_id' => $this->integer(),

            'PRIMARY KEY(post_id, tag_id)' <= PRIMARY KEY

        ]);

Does it make the query quicker ? Is it a must do for junction table ?

2

A primary key defines the order of the rows on disk. In general, it is good to consider how rows will be looked up and order the data using that column(s). In most cases, some kind of unique id is used to make it easy to identify one row and that is often the most common way to look up a row in a table.

Because junction tables point to two primary keys in two different tables, although you could include a normal primary key, it makes more sense to use two columns and form a composite key. This composite key will still be unique and should give you good lookup performance since most times the rows will be looked up via the other tables and not directly.

You do not have to have a primary key (in most cases you don’t, some database engines require it) but if you do not, then data is usually ordered by the insert time and that makes lookups very slow since you might have to perform an entire table scan to find the row you are after.

I’m not sure how much detail to go into because I don’t know what you already know about databases and query performance.

1 Like