General error: 1215 Cannot add foreign key constraint

Question

I'm trying to get price value from products table and set it to sold table. Can u please tell me why I'm getting this error ? product_id and user_id works fine. But I get error when I need to create foreign price in sold table

SOLD table

    Schema::create('sold', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');    
        $table->integer('product_id')->unsigned();  
        $table->foreign('product_id')->references('id')->on('products');
        $table->integer('price')->unsigned();
        $table->foreign('price')->references('price')->on('products');   
        $table->integer('bid_price')->unsigned();
        $table->foreign('bid_price')->references('bid_price')->on('products');     
        $table->timestamps();
    });

PRODUCTS table

    Schema::create('products', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->string('slug');
        $table->string('title');
        $table->text('body');
        $table->integer('price');
        $table->integer('bid_price');
        $table->string('address');
        $table->string('condition');
        $table->integer('quantity');
        $table->string('img_1');
        $table->string('img_2');
        $table->string('img_3');
        $table->string('img_4');
        $table->integer('views');
        $table->timestamps();
    });

Error message:

General error: 1215 Cannot add foreign key constraint (SQL
  : alter table `sold` add constraint sold_price_foreign foreign key (`price`
  ) references `products` (`price`))

EDIT

`LATEST FOREIGN KEY ERROR
------------------------
2016-03-01 12:40:08 31a0 Error in foreign key constraint of table auction/#sql-2564_ef:
foreign key (`price`) references `products` (`price`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.`

score 0 · Answer 1 · edited May 23 '17 at 12:31

Check this link: MySQL Cannot Add Foreign Key Constraint

The second answer there says:

I had set one field as "Unsigned" and other one not. Once I set both columns to Unsigned it worked.

So I would assume making the column "price" on your products unsigned too, should solve the problem.

Like this:

Schema::create('products', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on('users');
    $table->string('slug');
    $table->string('title');
    $table->text('body');
    $table->integer('price')->unsigned(); // <-- the difference is here!
    $table->integer('bid_price');
    $table->string('address');
    $table->string('condition');
    $table->integer('quantity');
    $table->string('img_1');
    $table->string('img_2');
    $table->string('img_3');
    $table->string('img_4');
    $table->integer('views');
    $table->timestamps();
});

Update:

The "LAST FOREIGN KEY ERROR" section tells you:

Cannot find an index in the referenced table

And the MySQL documentation says about foreign keys:

MySQL requires indexes on foreign keys and referenced keys

So basically you have to add an index to the price column in the products table.

I never worked with Laravel, but according to their documentation it should work like this:

$table->index('price');

Update 2: I only considered the technical aspect here, but looking at the database structure in a semantical way, it is probably a bad idea to create a foreign key over price columns, because a price is not really an identifier for a product (products having the same price do exist!).

Can you run the "SHOW ENGINE INNODB STATUS" query (as suggested by the accepted answer of the linked question) and tell us the content of the "LATEST FOREIGN KEY ERROR" section? — Tobias Xy, Mar 01 '16 at 10:42
Yes. Run it on the database directly (from command line or in a frontend like PhpMyAdmin). — Tobias Xy, Mar 01 '16 at 10:49
Yeah, I get the product_id and I'll just post the other values without foreign. Thanks for help — hulkatron, Mar 01 '16 at 11:11

score 0 · Answer 2 · answered Mar 01 '16 at 11:02

0

Cannot find an index in the referenced table

Your products table needs an INDEX on the column you are trying to reference. I'd say this has to be unique otherwise MySQL is unable to determine which row it is referencing.

answered Mar 01 '16 at 11:02

thisisboris

431
1
3
13

@hulkatron I think you shouldn't use a foreign key reference at all for your price. Foreign keys are used for references to a data-row, not a separate field. Your error states that it can't create the foreign key as the foreign field is not an index. – thisisboris Mar 01 '16 at 11:06
Maybe it's true, maybe I just should post the values when item is sold – hulkatron Mar 01 '16 at 11:07
I'd suggest that too. Or you could simply reference your product, then you don't have to keep track off the fields in that product. Unless of course these fields tend to change, then it would be better to just copy the values into the column rather than referencing. – thisisboris Mar 01 '16 at 11:09

General error: 1215 Cannot add foreign key constraint

2 Answers2