Level 39
Sure that tax_rate is not part of a foreign key?
If yes, save your db, recreate it (php artisan migrate:refresh), retest
Feb 11, 2021
9
Level 2
SQLLite fails in tests with General error: 1 foreign key mismatch
Hello, I want to change the columns datatype from integer to decimal and have written a simple migration:
Schema::table('products', function (Blueprint $table) {
$table->decimal('tax_rate', 6, 2)->change();
});
The migration runs smoothly when executing php artisan migrate. When I run my tests with php artisan test it fails with:
SQLSTATE[HY000]: General error: 1 foreign key mismatch - "order_slots" referencing "products" (SQL: INSERT INTO products (id, sku, name, active, tax_rate, created_at, updated_at) SELECT id, sku, name, active, tax_rate, created_at, updated_at FROM __temp__products)
Why is this happening and how can I solve this? I am using SQLlite locally for the test environment and when I google it, I stumble across some who might have the same issue, but I can not see any solution to this?
Can anybody help?
Level 73
What does your original migration look like? It has a foreign key in some table and the two types are a mismatch.
Level 2
I am a step further, I think the issue is that SQLite does not support to change columns. So I have to drop it and recreate a new one. But still this does not work:
DB::statement('ALTER TABLE products RENAME COLUMN tax_rate TO _tax_rate;');
Schema::table('products', function (Blueprint $table) {
$table->decimal('tax_rate', 6, 2)->nullable();
});
DB::statement('UPDATE products SET tax_rate = _tax_rate;');
Schema::table('products', function (Blueprint $table) {
$table->dropColumn('_tax_rate');
});
1 like
Level 73
You are correct you can not modify columns in SQLite.
What is it that is not working for you? Do you still get the foreign key error?
What tables reference the products table? Make sure that all fields that references the tax_rate column has the same type.
Level 2
Hey @tray2, when I do it as in the post above I get still this error, but this error comes now from the dropColumn Statement.
SQLSTATE[HY000]: General error: 1 foreign key mismatch - "order_slots" referencing "products" (SQL: INSERT INTO products (id, sku, name, active, tax_rate, created_at, updated_at) SELECT id, sku, name, active, tax_rate, created_at, updated_at FROM __temp__products)
When I remove the dropColumn and do it like this:
DB::statement('ALTER TABLE products RENAME COLUMN tax_rate TO _tax_rate;');
Schema::table('products', function (Blueprint $table) {
$table->decimal('tax_rate', 6, 2)->nullable();
});
DB::statement('UPDATE products SET tax_rate = _tax_rate;');
And I check it with:
$d = DB::select('SELECT c.name FROM pragma_table_info("products") c');
dd($d);
I have the columns _tax_rate and tax_rate. When I leave out to drop the column I get this error
Integrity constraint violation: 19 NOT NULL constraint failed: products._tax_rate
Which makes sense, because I do not have this column in the factory class. But I do not reference tax_rate anywhere, especially not in the order_slots table. The order_slots table has a relation to products.
I checked further and dropColumn is only supported with doctrine/dbal, but this package is installed via composer. And now I am stuck.
Do you have any idea how to fix this?
Level 73
What does your order_slots migration look like?
Level 2
Like this:
class CreateOrderSlotsTable extends Migration
{
public function up()
{
Schema::create('order_slots', function (Blueprint $table) {
$table->id();
$table->foreignId('order_template_id')->constrained()->onDelete('cascade');
$table->foreignId('layout_slot_id')->constrained();
$table->string('sku');
$table->foreign('sku')->references('sku')->on('products')->onDelete('no action');
$table->integer('quantity');
$table->timestamps();
});
}
Level 3
@elbsurfer Have you ever overcome this problem?
Level 2
If I recall correctly, we have moved from SQLite to MySQL in our tests... :)
Please or to participate in this conversation.