Is it possible to create a foreign key from another database?

Asked

Viewed 547 times

5

I want to reference a foreign key from a different bank, saw on some forums that it is only necessary to do `nome_database`.`nome_tabela`, but it’s not working.
My code is this::

ALTER TABLE `relatorio_gestao`.`usuario_setores` 
ADD CONSTRAINT `id_unidade_ifpe`
FOREIGN KEY (`id_unidade_ifpe`)
REFERENCES `bancobase`.`unidade_ifpe` (`id_unidade_IFPE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

What I’m doing wrong?

mysql database mysql-workbench

  • There are no mistakes in the construction of FOREIGN KEY. A database is relatorio_gestao and the other is bancobase? The field name in the table unidade_ifpe is id_unidade_IFPE (capital)?

  • I created the Databases and tables on my machine and it worked perfectly. Your code is correct. Are the two tables INNODB? Are the columns of the same type and have the same size? When you reply, put comment and at the beginning put arroba and name_do_usuario (Lucas Henrique) to notify me.

1 answer

1

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

In the basic documentation of Mysql does not provide this syntax database.table, I know that for example Mysql accepts check constraints but these have no effect , perhaps it is the same case.

In Oracle I implemented something like this through triggers, making all integrity checks by selects, laborious but worked.

Browser other questions tagged mysql database mysql-workbench

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