Linking two tables of different databases

[jcrivera]

I have two databases in the same SQL Server 7.0. To keep data integrity among tables, I would like to link a table from one with a table of the other using FKs/PKs. I know I can't do this by the use of Diagrams, and also tried manually by attempting an ALTER TABLE using the Query Analyzer... it didn't work this way either.

Could somebody give me some hint?

J.C.
[sig][/sig]

 

[karluk]

The last time I tried to do this was in SQL Server 6.5, and it turned out that foreign keys weren't allowed between tables in different databases. It sounds as if this limitation still applies to SQL Server 7.0. [sig][/sig]

 

[foxdev]

From the SQL Server 7 BOL:
"A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table..."

An alternate way would be having an Insert and Update trigger that executes a query on the foreign table, then rolls back if the record is not found. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[jcrivera]

Hi again Robert...
Your alternative sounds good, but could u be a bit more specific? I'm a bit confused.

Thank you.

J.C. [sig][/sig]

 

[TomSark]

You need to use triggers to enforce RI across different databases, whether they exist on the same or different server.


Tom [sig][/sig]

 

[jcrivera]

Greetings Tom...

Indeed the use of triggers to enforce RI by now is the last bullet I got on the shotgun, as I was originally considering. Thank God the databases co-exist in the same server and in this way triggers are the alternative, cuz if we were talking about different servers, this couldn't have worked either given that my SQL-OLEDB provider doesn't support distributed transactions between two servers.

Thanks for your assistance.

J.C.

 

[foxdev]

J.C., I think Tom expertly answered your query for more details, but if you need more, let us know.

As for the scenario where the second database resided on a non-SQL Server database, I don't know that the lack of DTS support would be a factor. Assuming you can do a linked-server query from within a trigger (I haven't tried, but I don't know why not), then it should be feasible. Probably poor performance, but feasible.

Robert Bradley

http://www.foxdev.com/

 

[calahans]

How static is you data? Could you have a nightly job to replicate the data between the two databases (enforce the relationship on the copy table).

If two tables are joined in an FK relationship I would suggest they reside in the same database.

Also, you may have to look to a provider for distibuted transactions if your business model requires it.

Cal

 

[TomSark]

OK... so how do I get those smiley faces in my messages? I'm new to the forum...

Tom

 

[jcrivera]

Sorry for the possible misunderstanding... :-|

When I said that my OLEDB provider is not allowing me a distributed transaction between two servers, I didn't mean that one was SQL and the other was non-SQL. Indeed, both of them are SQL 7.0. Evenmore, they are linked with each other. So why the provider is not letting me to do this kind of transactions...? Not a clue.

What I'll definitely try is to use triggers to enforce RI.