Foreign Key between different databases

Asked

Viewed 265 times

5

Is it possible to link two different databases in a Migration? I am using Postgres.

What do I have:

$table->integer('id_cliente_produto')->unsigned();
$table->foreign('id_cliente_produto')->references('id_cliente_produto')->on('clienteproduto');

I need something like:

$table->integer('id_cliente_produto')->unsigned();
$table>foreign('id_cliente_produto')>references('id_cliente_produto')->on('Banco2.tabela');
laravel postgresql

  • These different databases are on different servers?

  • exactly, are even on different servers.

  • Outside the framework you can do this in Postgres ?

  • Yes, using db_link.

  • It is impossible to define a FOREIGN KEY between tables of different databases in Postgresql, however you can define it between tables of different schemas of the same database. With db-link, or even a Foreign Data Wrapper, you can access a table from another database but cannot create an integrity restriction between them.

2 answers

0

To use two different databases in the same project:

1 - In your file . env put your variables of the new bank

DB_ANOTHER_HOST=localhost
DB_ANOTHER_DATABASE=banco2
DB_ANOTHER_USERNAME=root
DB_ANOTHER_PASSWORD=

2 - Now in your config/database.php add this connection:

'banco2' => [
            'driver'    => 'mysql',
            'host'      => env('DB_ANOTHER_HOST', 'localhost'),
            'database'  => env('DB_ANOTHER_DATABASE', 'forge'),
            'username'  => env('DB_ANOTHER_USERNAME', 'forge'),
            'password'  => env('DB_ANOTHER_PASSWORD', ''),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'strict'    => false,
            'engine'    => null,
        ],

3 - Finally in your entity file use this new connection:

class Cadastro extends Model
{
    protected $connection = 'banco2';
}

4 - So the Register model will be in bank 2 and all my system will be able to account for the default bank.

5 - Last but not least, do not use your column in the main bank as foreign key, avoid using foreign keys, this is an example, always in your model do the interface:

public function Enderecos() {
   return $this->hasMany(Cadastro:class, 'id', 'cadastro_id');
}

  • The answer is OK to relate by Eloquent, who will take care of mapping the links, but the question was about Migration - creation of the database structure, ie creation of referential integrity by means of foreign keys between different databases with Postgresql.

0

I never had to do this, but given a search on google I found this solution (I did not test to see if it really works):

$table>foreign('id_cliente_produto')>references('id_cliente_produto')->on(new Illuminate\Database\Query\Expression('Banco2.tabela'));

Recommendation: put the reference to the name of the bank in some configuration file, in case the name of the bank changes, you only need to change one file.

Browser other questions tagged laravel postgresql

You are not signed in. Login or sign up in order to post.