Will Eloquent automatically add this foreign key to the primary key's index?

0
votes

Let's say I have a parent and a child table, in Laravel, for table Order, my model would look like this:

public function up()
{
    Schema::create('orders', function(Blueprint $table)
    {
        $table->integer('customer_id')->unsigned();
        $table->foreign('customer_id')->references('id')->on('customers')->onDelete('cascade');

        $table->increments('id');

I know Eloquent would consider id to be the primary key of orders, so an index would be automatically created on that primary key.

What should I do to make sure that customer_id is part of the primary key's index, setup in that order:

1.  customer_id
2.  id

Example of tables

      Customer
    +-------------+
    | id          | --> primary key
    |- - - - - - -|
    | name        |
    | address     |
    +-------------+
           |
           |
           |
           A
      Order     
    +------------------+        
    | customer_id (fk) | --- primary key
    | id               | --- primary key        
    |- - - - - - - - - |
    | date             |
    +------------------+
2
laravellaravel-5eloquent
Try to add $table->primary(array('id', 'customer_id')); ! – Maraboc
Refer below link. I hope it will help you. stackoverflow.com/questions/22477726/… – Akshay Deshmukh
Is there a reason why you need it as part of the primary key's index? This isn't making a lot of sense to me. If you are just looking for efficient joins, the foreign key should be enough and you can verify it by doing an explain select... – user1669496
My idea is that it would be faster for MySQL to access orders through the primary key index (customer_id, id) when I query the order table with customer_id, since customer_id would be the first column of the primary key index. I've worked on DB2 and it was done that way, but i'm guessing MySQL does it differently? – user3489502

2 Answers

2
votes

Will Eloquent automatically add this foreign key to the primary key's index?

Well, not automatic but its very easy.

To specify custom primary key, you can call primary() method from Blueprint class, called through $table. ie $table->primary().

For a single primary key, it accepts a string specifying the name of column to be made primary.

For composite key, you can pass an array of strings of the columns to be made primary. In your case

$table->primary( ['id', 'customer_id'] )

0
votes

I decided to try this out and see what happens.

Starting with the customers table, I ran this statement...

CREATE TABLE customers (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME,
    PRIMARY KEY (id)
);

I then created the following...

CREATE TABLE orders (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT(11) UNSIGNED,
    created_at DATETIME,
    updated_at DATETIME,
    PRIMARY KEY (id, customer_id)
);

Note here if you use PRIMARY KEY (customer_id, id), it will result in a SQL error. This makes me believe the DB2 functionality you are trying to replicate will not work exactly the same on MySQL and we actually need a foreign key.

Then after filling these tables with test data, I ran the following...

EXPLAIN SELECT *
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

This results in 

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------------------------------------------+
| id   | select_type | TABLE     | TYPE | possible_keys | KEY  | key_len | ref  | ROWS   | Extra                                           |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------------------------------------------+
|    1 | SIMPLE      | customers | ALL  | PRIMARY       | NULL | NULL    | NULL |      4 |                                                 |
|    1 | SIMPLE      | orders    | ALL  | NULL          | NULL | NULL    | NULL | 262402 | USING WHERE; USING JOIN buffer (flat, BNL JOIN) |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------------------------------------------+

I then added the foreign key...

ALTER TABLE orders ADD FOREIGN KEY customer_id (customer_id) REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE;

And running the same exact explain query before with the same exact data, I now get the results...

+------+-------------+-----------+------+---------------+-------------+---------+-----------------------+-------+-------+
| id   | select_type | TABLE     | TYPE | possible_keys | KEY         | key_len | ref                   | ROWS  | Extra |
+------+-------------+-----------+------+---------------+-------------+---------+-----------------------+-------+-------+
|    1 | SIMPLE      | customers | ALL  | PRIMARY       | NULL        | NULL    | NULL                  |     4 |       |
|    1 | SIMPLE      | orders    | ref  | customer_id   | customer_id | 4       | cookbook.customers.id | 43751 |       |
+------+-------------+-----------+------+---------------+-------------+---------+-----------------------+-------+-------+

As you can see, much fewer rows are being evaluated when I add the foreign key which is exactly what we are looking for. Surprising for me probably because I'm not a DBA, running the following produces the same results...

EXPLAIN SELECT * FROM orders WHERE customer_id = 4;

Even in this case, the composite primary key isn't doing anything for you however the foreign key is helping immensely.

With all that said, I think it's safe to forego the composite primary key and just set the id up as primary and set the customer_id up as a foreign key. This also gives you the benefit of being able to cascade deletes and updates.