I'm having trouble inserting an entry into an SQLite3 table with Laravel (using version 11.1.0), specifically giving my a Foreign Key Constraint violation.
I have a Laravel Project using version 11.1.0, and it's set up with the following migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateAllTables extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
// Create users table
Schema::create('users', function (Blueprint $table) {
$table->string('user_id')->primary();
$table->string('username')->notNullable();
// Add other user-related fields as needed
$table->timestamps();
});
// Create routes table
Schema::create('routes', function (Blueprint $table) {
$table->increments('route_id');
$table->text('polyline_data')->nullable();
$table->timestamps();
});
// Create shopping_lists table
Schema::create('shopping_lists', function (Blueprint $table) {
$table->increments('list_id');
$table->integer('user_id')->notNullable();
$table->string('name')->notNullable();
$table->integer('route_id')->unsigned()->nullable();
// Foreign keys
$table->foreign('user_id')->references('user_id')->on('users');
$table->foreign('route_id')->references('route_id')->on('routes');
$table->timestamps();
});
// Create grocery_items table
Schema::create('grocery_items', function (Blueprint $table) {
$table->increments('item_id');
$table->string('name')->notNullable();
$table->integer('quantity')->nullable();
$table->boolean('is_food')->default(false);
$table->integer('shopping_list_id')->unsigned();
$table->foreign('shopping_list_id')->references('list_id')->on('shopping_lists');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('grocery_items');
Schema::dropIfExists('shopping_lists');
Schema::dropIfExists('routes');
Schema::dropIfExists('users');
}
}
I have run this migration and I'm currently trying to insert into the shopping_lists table (this table contains the titles for shopping lists) by creating a list item with this controller:
<?php
namespace App\Http\Controllers\Api;
use App\Http\Controllers\Controller;
use App\Models\ShoppingList;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;
use App\Models\Route; // Import the Route model
use Illuminate\Support\Facades\Schema; // Necessary for debugging the schema
class ShoppingListController extends Controller
{
public function index()
{
return response()->json(ShoppingList::all(), 200);
}
public function store(Request $request)
{
Log::info("Received shopping-lists request from IP address " . $request->ip());
$userId = $request->user_id;
Log::info("User ID retrieved from JWT: " . print_r($userId, true));
// Validate the request data
$validatedData = $request->validate([
'name' => 'required|string',
'route_id' => 'nullable|integer', // Validate route_id as nullable and a valid UUID
]);
Log::info("Validated request: " . print_r($validatedData, true));
// Check if user_id exists
/*$user = DB::table('users')->where('id', $userId)->first();
if (!$user) {
Log::error("User ID $userId does not exist.");
return response()->json(['error' => 'Invalid user_id'], 400);
}*/
// Check if route_id exists (if provided)
if (!empty($validatedData['route_id'])) {
$route = DB::table('routes')->where('id', $validatedData['route_id'])->first();
if (!$route) {
Log::error("Route ID {$validatedData['route_id']} does not exist.");
return response()->json(['error' => 'Invalid route_id'], 400);
}
}
// Generate a UUID for route_id if not provided
if (empty($validatedData['route_id'])) {
$validatedData['route_id'] = null;
}
$newEntry = [
'user_id' => $userId,
'name' => $validatedData['name'],
'route_id' => $validatedData['route_id'],
];
Log::info("Entering new entry into Shopping List database: " . print_r($newEntry, true));
try {
$shoppingList = ShoppingList::create($newEntry);
Log::info("Created shopping list: " . $shoppingList);
} catch (\Exception $e) {
Log::error("Error creating shopping list: " . $e->getMessage());
return response()->json(['error' => 'Could not create shopping list'], 500);
}
return response()->json($shoppingList, 201);
}
public function show(ShoppingList $shoppingList)
{
return response()->json($shoppingList, 200);
}
public function update(Request $request, ShoppingList $shoppingList)
{
$request->validate([
'name' => 'required|string',
'route_id' => 'nullable|exists:routes,route_id',
]);
$shoppingList->update($request->all());
return response()->json($shoppingList, 200);
}
public function destroy(ShoppingList $shoppingList)
{
$shoppingList->delete();
return response()->json(null, 204);
}
}
But it keeps giving me this error in the logging, which clearly shows it's providing all fields for the insert:
[2024-05-26 17:06:07] local.ERROR: Error creating shopping list: SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (Connection: sqlite, SQL: insert into "shopping_lists" ("user_id", "name", "route_id", "updated_at", "created_at") values (113663583418864754012, first grocery list test, ?, 2024-05-26 17:06:07, 2024-05-26 17:06:07))
I checked and there's already a user with this id value stored in my users table, so the only other field that could be giving foreign key problems should be the route_id field. I've already scowered other stackoverflow posts but couldn't find anything to solve this specific problems. Any help will be greatly appreciated.
On your user table you have defined user_id as string, whereas on your shopping_lists table you have defined it as integer.
Try defining it as string on the shopping_lists as well, either by creating an alter migration (https://laravel.com/docs/11.x/migrations#modifying-columns) or by going one back and fixing the one you have (although the second option will also drop the data you already have in the tables).
An alter migration would look like the following:
Schema::table('shopping_lists', function (Blueprint $table) {
$table->string('user_id')->change();
});