Foreign Key to External Database

[TGM]

Hi

I use one SQL database (caleed Db1) with a table called 'UnitData'.This table have a field called 'UnitId'
I would like to use the table 'UnitData' in a second database (called Db2) to enforce relationship inside this second db.

I create the following script to build a table that would use a foreign key to the UnitData table

Create Table BatchData
{
BatchId .....
BatchNbr blabla ...
UnitId int NOT NULL CONSTRAINT Fk_.... REFERENCES Db1..UnitData( UnitId)
...
}

I got the following error
Cross-database foreign key references are not supported

How could I do such thing using MS-SQL ?

Thanks for your help

Thierry


}

 

[JohnDTampaBay]

You may be able to create an INSERT/UPDATE trigger on BatchData to check the foreign key in the second database, and rollback the transaction if the key is not found.

Need a hand with the trigger code?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[TGM]

Hello Angel

Yes, please ...

Well, I don't know if I will be able to implement this kind of solution since I need to 'share' several tables...

So another solution is to simply put everything in the same database.

Thanks in advance for your code

Thierry

 

[JohnDTampaBay]

Here's a very simple example. If you have many tables that require foreign key constraints like this, it will be a lot easier to have the tables all in the same database. Triggers can be tricky to work with sometimes. Let the foreign key constraint do its job.

CREATE TRIGGER trgIUBatchData
ON BatchData AFTER INSERT, UPDATE
AS BEGIN
  IF EXISTS(SELECT UnitID FROM Inserted
            WHERE UnitID NOT IN (SELECT UnitID
                                 FROM Db1..UnitData)) BEGIN
      ROLLBACK TRANSACTION
      RAISERROR('UnitID foreign key constraint violated.',16,1)
  END
END

Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[TGM]

Thanks, Angel ...

I'll keep the code and try it.

Thierry