Add Foreign Key Relationship Between Two 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?

Using column name as part of foreign key relationship

You can have a constant stored computed column for the FK.

CREATE TABLE clients 
(
id int,
table_id int,
status TEXT,
refcolname text GENERATED ALWAYS AS ('status') STORED,
FOREIGN KEY (table_id, refcolname, status)
REFERENCES dropdowns (table_id, field_name, value)
);

Really I would advice against the design like that. Why to create a united table of lookups just to get troubles referencing its parts. Use the EAV pattern only if it is absolutely inevitable, for example a set of lookup tables is to be defined by a user at runtime.

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.

Many-to-Many Relationship between two tables in two different databases

The standard way of using foreign key constraints to enforce referential integrity is only possible within the same database - not db cluster. But you can operate across multiple schemas in the same database.

Other than that, you can create tables just the same way. And even join tables dynamically among remote databases using dblink or FDW. Referential integrity cannot be guaranteed across databases by the RDBMS, though.

Does not matter much whether the other DB is on the same physical machine or even in the same DB cluster - that just makes the connection faster and more secure.

Or you can replicate data to a common database and add standard constraints there.

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.

Failed to create table because of foreign key constraints

The foreign key should be on the movie_genre table, not the genres table

Add multiple primary keys to a table

I find one solution.

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='avgEnt'
and type_desc LIKE '%CONSTRAINT'

Find constraint of table PK_avgent

After drop primary on table

-- drop current primary key constraint
ALTER TABLE dbo.avgEnt
DROP CONSTRAINT PK_avgent;
GO

After add two column

-- create new primary key constraint
ALTER TABLE dbo.avgEnt
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (SrNo, TrnDate);
GO

it is work form me.



Related Topics



Leave a reply



Submit