How can I set a foreign key constraint pointing a table in a different schema?

Admin 1
Viewed 592

I'm relatively new to MySQL syntax. I'm trying to set up a database with different schemas. For convenience I structured my code in two parts:

  • in the first part I'm creating the tables in each schema without imposing foreign key constraints
  • in the second part I'm writing ALTER TABLE commands to create foreign key relations.

When it comes to create a foreign key constraint for an ID in a table that point to a table in another schema I get

"Error Code: 1824. Failed to open the referenced table 'schema_a.table_a".

Practically, I've done this:

#First part
CREATE SCHEMA `schema_a`
USE `schema_a`;
CREATE TABLE `table_a`
(
    IDa INTEGER NOT NULL,
    ...
    PRIMARY KEY(`IDa`)
 )ENGINE=InnoDB, CHARSET=..., Collate=...;


CREATE SCHEMA `schema_b`
USE `schema_b`;
CREATE TABLE `table_b`
(
    IDb INTEGER NOT NULL,
    IDa INTEGER NOT NULL,
    ...
    PRIMARY KEY(`IDb`)
)ENGINE=InnoDB, CHARSET=..., Collate=...;

#Second part
USE `Schema_b`;
ALTER TABLE `table_b`
ADD CONSTRAINT `FK_IDa` FOREIGN KEY (`IDa`) REFERENCES `schema_a.table_a`(`IDa`) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not even sure if it is formally correct to create external foreign keys.

Does anyone have any suggestions to solve this problem? Thanks!

foreign-keys mysql database
1 Answers

`schema_a.table_a` means one identifier, i.e. MySQL assumes that only the table name is schema_a.table_a. You need to quote each part of a multi part identifier individually like in `schema_a`.`table_a`.

ALTER TABLE `table_b`
            ADD CONSTRAINT `FK_IDa`
                           FOREIGN KEY (`IDa`)
                                       REFERENCES `schema_a`.`table_a`
                                                  (`IDa`)
                                       ON DELETE CASCADE
                                       ON UPDATE CASCADE;

Or you can just not quote in your particular case.

user_96614240
Related