Level 75
Shouldn't any foreign keys be in the same database?
Also see https://stackoverflow.com/questions/25952348/laravel-run-migrations-on-another-database
Oct 14, 2024
4
Level 1
Migration in the Laravel framework when using two databases
Hi everyone. I have a Laravel project that uses two databases. In the main database, I store the tables that contain the list elements (e.g., statuses, types, categories). I use these list elements in forms. If I start a migration for a form in the main database and place the following line in the migration file, the migration runs without errors, and the table is added to the database (row: $table->foreignId('state_id')->constrained('list_states')->cascadeOnDelete();). However, if I reference a list element from the main database in the other database, the migration throws an error and doesn’t run.
Example list element migration code:
public function up(): void
{
Schema::create('list_states', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug');
$table->boolean('is_visible')->default(false);
$table->softDeletes();
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('list_states');
}
Example property_list migration code (This table is added to the main database, where the list element table is also located.):
public function up(): void
{
Schema::create('property_lists', function (Blueprint $table) {
$table->id();
$table->longText('description');
$table->foreignId('state_id')->constrained('list_states')->cascadeOnDelete();
$table->foreignId('city_id')->constrained('list_cities')->cascadeOnDelete();
$table->text('seo_title')->nullable();
$table->text('seo_meta_description')->nullable();
$table->timestamps();
$table->softDeletes();
});
}
public function down(): void
{
Schema::dropIfExists('property_lists');
}
This table would be migrated to the second database, but it results in an error because the list_states table is not present or migrated in this database (of course, in the list_states model file, I specified that the table data should be fetched from the main database with protected $connection = 'mysql';).
public function up(): void
{
Schema::create('customers', function (Blueprint $table) {
$table->id();
$table->text('name')->nullable();
$table->integer('age')->nullable();
$table->text('bio')->nullable();
$table->foreignId('state_id')->constrained('list_states')->cascadeOnDelete();
$table->foreignId('city_id')->constrained('list_cities')->cascadeOnDelete();
$table->timestamps();
$table->softDeletes();
});
}
public function down(): void
{
Schema::dropIfExists('property_lists');
}
State Model code:
class State extends Model { use HasFactory;
protected $connection = 'mysql';
protected $table = 'list_states';
protected $fillable = ['name', 'slug', 'is_visible'];
public function cities()
{
return $this->hasMany(\App\Models\List\City::class, 'state_id');
}
}
Error msg:
General error: 1824 Failed to open the referenced table '$list_states' (Connection: tenant, SQL: alter table property_lists add constraint property_lists_state_id_foreign foreign key (state_id) references $list_states (id) on delete cascade)
Level 1
@jlrdw Maybe this could be a solution:
class MigrationBlah extends Migration { public function up() { $dbs = DB::connection('tenants')->table('tenants')->get(); foreach ($dbs as $db) { Schema::table($db->database . '.bodegausuarios', function($table){ $table->foreign('usuario')->references('usuarioid')->on('authusuarios'); }); } } }
I use the tenancyforlaravel add-on to generate tenants, so the tenant database is created automatically.
Level 55
Option 1: Ditch the Foreign Key Constraint You won’t be able to enforce foreign key constraints between databases. But you can still reference the state_id without the constrained() method. Basically, you drop the foreign key constraint and handle it with business logic or model relationships.
public function up(): void
{
Schema::create('customers', function (Blueprint $table) {
$table->id();
$table->text('name')->nullable();
$table->integer('age')->nullable();
$table->text('bio')->nullable();
$table->foreignId('state_id'); // Just referencing the state_id without constraint
$table->foreignId('city_id'); // Same for city_id
$table->timestamps();
$table->softDeletes();
});
}
So, Laravel won't throw errors during migration, and you can still handle the logic in your models. This is pretty common when dealing with multi-database systems.
Option 2: Manual Foreign Key Checks If you really want to enforce some integrity, you can manually check in your models or application code whether the state_id exists in the other database, before inserting or updating records.
Option 3: Sync Data If you really need those foreign keys and constraints, one alternative is to duplicate the data from list_states into the second database. This can be overkill, but in some setups, syncing the tables makes sense to keep things simple.
So basically, the issue here is trying to use foreign keys across databases, which isn’t supported. You’ll have to work around it by either not using constraints or duplicating data.
1 like
Level 1
@azimidev Thank you for your reply.
So, is this the correct solution:
$table->foreignId('state_id');
$table->foreignId('city_id');
I will handle the list ID verification manually then.
Please or to participate in this conversation.