Match ID between two different databases in Laravel

Question

I would like to try how to match between two different databases in Laravel.

I am using Laravel in 5.7 version.

To simplify my databases, let's say a table is called Employee in DB1 and the other table is called Worker in DB2.

A primary key in both tables is ID which should be matched.

If Laravel doesn't allow that, I am also willing to know how to match in mySql.

I am a little confused - Laravel is a PHP framework. Isn't this a MySQL question? — Michael Nelles, Mar 30 '20 at 21:53
@Nelles If I can handle with it in Laravel, I would like to know how to do that. if can't, I am also likely to try it in mysql — , Mar 30 '20 at 21:57

score 0 · Accepted Answer · answered Mar 30 '20 at 23:29

Quoted from this answer here

You would need to manage the referential constraint across databases using a Trigger. ***************** Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:
Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END
Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

As mentioned in the quoted answer, the best approach would be to have the tables in the same database. If you can do that then simply add a foreign key between them:

The FOREIGN KEY constraint is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

So in this example column named PersonID located in table named Orders will link via a foreign key to the column named PersonID located in table named Persons.

Match ID between two different databases in Laravel

1 Answers1