Landslyde Landslyde - 1 month ago 9
MySQL Question

How can I reference a Foreign Key from db1 in db2 using MySQL

I have two MySQL databases:

master
and
slave
. The
master
db contains the
memberid
field. How can I reference
memberid
in the
slave
db? This is what I've tried using the
slave
db with no success:

DROP TABLE IF EXISTS `attend_date_temp`;
CREATE TABLE `attend_date_temp` (
`clientid` int(10) unsigned NOT NULL,
`groupid` int(10) unsigned NOT NULL,
`memberid` int(10) unsigned NOT NULL,
KEY `clientid` (`clientid`),
KEY `memberid` (`memberid`),
CONSTRAINT `attend_date_temp_ibfk_1` FOREIGN KEY (`clientid`) REFERENCES `clients` (`clientid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `attend_date_temp_ibfk_2` FOREIGN KEY (`memberid`) REFERENCES `master.members` (`memberid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8


When I view the
memberid
foreign key in that table, I get this message:

Table 'slave.master.members' doesn't exist.


Can someone tell me what I'm doing wrong? This is the reference I used.
Thanks.

Answer
REFERENCES `master.members`

should be:

REFERENCES `master`.`members`

Otherwise, it tries to find a table called master.members in the slave database.

Comments