Error on migrate in postgresql database

[daab]

SQLSTATE[42830]: Invalid foreign key: 7 ERROR: there is no unique constraint that matches the given columns in the referred table «currencies» (Connection: pgsql, SQL: alter table "accounts" add constraint "accounts_currency_code_foreign" foreign key ("currency_code") references "currencies" ("code") on delete set null).

The same error in the creation of the accounts, company_defaults and contacts tables.

[andrewdwallo]

I've investigated the foreign key constraint issue with PostgreSQL and found a solution. The issue is due to PostgreSQL's strict requirements for composite foreign key constraints.

Temporary Fix

You'll need to adjust the foreign key definition in the accounts table (and others) to match the order of columns in the currencies table's composite unique index. Here's what you should change:

In the accounts table migration (along with others), find the foreign key definition that references the currencies table. Update it to match the order of columns as they appear in the currencies table's unique constraint. It should be updated to look like this:

$table->foreign(['company_id', 'currency_code'])
                ->references(['company_id', 'code'])
                ->on('currencies')
                ->restrictOnDelete();

This adjustment aligns with PostgreSQL's requirements and should resolve the foreign key constraint issue you're experiencing.

I will be updating the application with this fix sometime soon, but this change should help you overcome the immediate issue.

If you have any further questions or need more assistance, feel free to reach out.

[daab]

Yes, this solves the problem.