How to Create Foreign Keys Across Databases

Add Foreign Key relationship between two Databases

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.

Having foreign keys between two different databases using linked servers?

No, it is not possible to create foreign keys between objects in different databases (even if they are on the same server). The official documentation is pretty clear about that:

FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).

It even points you to the possible workaround, i.e. to try to implement some kind of referential integrity checks using triggers. You can add after insert/update triggers on both sides to validate the data changes, and after delete triggers on the primary table to check are there child records. If the validation fails, you will raise an error. You can also use instead of triggers.

But the solution with triggers will not guarantee the referential integrity anyway. You can lose connectivity between databases. You can restore one of the databases from older backup. All kind of things can go wrong. You better try to reconsider your database design. Is it possible to combine these two databases into one? Is it possible to maintain copies of both tables into each of the databases and try to replicate stuff?

Can I create Foreign Keys across Databases?

No, Oracle does not allow you to create a foreign key constraint that references a table via a database link. You would have to use triggers to enforce the integrity.

Foreign key across multiple databases

You can have a composite foreign key constraint:

FOREIGN KEY (first_name, last_name)
REFERENCES accounts.users (first_name, last_name)
ON UPDATE CASCADE

Although, as @Furqan comments, there are probably better ways to achieve what you want.

MySQL InnoDB foreign key between different databases

I do not see any limitation on https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html.

So just use otherdb.othertable and you will be good.

PostgreSQL FOREIGN KEY with second database

I've not had occasion to use this myself, but you might want to look into Foreign Data Wrappers, which are essentially the successor to dblink. In particular, postgres-fdw.

Once the general setup of the fdw is in place (steps 1-3 in the link above), you could create a foreign table via CREATE FOREIGN TABLE, defined like the table in your remote DB, and then use that table as part of the foreign key CONSTRAINT, and see if it works.

If that doesn't work, another option would be to have a process which ETL's the data (say, via a Python script) from the remote server over to the local server (say, on an hourly or daily basis, depending on the size), and then you would have a true local table to use in the foreign key CONSTRAINT. It wouldn't be real-time, but depending on your needs, may suffice.

SQL Foreign Key in Another Database

  • Triggers (as you mention)...
  • Database Partitioning...
  • Duplicate the tables from MasterC (are they transactional? How often are they updated? Does MasterC need the updates? If so, can you allow for the copies to be updated every 24-hours, etc. via a "Job"?)

Per your last comment about individual builds, I guess I'd say that the threshold for when a client "needs" both is when they...well, "need" both. Sounds almost like you need a 3rd schema, for tables in MasterC that do NOT reference MasterF, for those clients you're categorizing as not needing both.

BTW -- this is all about referential integrity, yes? There are other strategies you could employ toward that goal, besides 'relationships'.

EDIT

CREATE TRIGGER myTrigger ON myTable
AFTER INSERT
AS
IF NOT EXISTS (SELECT * FROM OtherDatabase.otherschema.othertable F
JOIN inserted AS i
ON F.KeyYouAreLookingFor = i.KeyYouHave)
BEGIN
RAISERROR ('Lookup Value Not Found -- Insert Failed', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

Service Oriented Architecture: Foreign Key Across Different Databases

You can use a Guid(UUID) as the key of your entities, generated by the creator and then you can use that Guid across databased and tables to reference the entity in question.

for example, when you create a new order, the ui will generate the order's Id, send a message to the component creating the order, the component will then publish an event using that orderId so that the other components can do related work to that order without accessing the database to get that id.

Make sense?

How to set Foreign Key in mysql between Two different databases?

Any relationship between/among tables are confined within a schema. You cannot define foreign key constrain between two unrelated tables in two different schema. If you have a real need o do it then you need to re-think about your database design.



Related Topics



Leave a reply



Submit