CODEWITHSUNDEEP
sqloracle-databaseplsql
user3183221
user3183221

Reputation: 1

Create Table using references to two tables

I have a distributed data base with two nodes. I have a table like this one in node2 (only in this node):

CREATE TABLE table2
(
    cod_proveedor CHAR(15) REFERENCES proveedor(cod_proveedor),
    cod_articulo  CHAR(15) REFERENCES articulo(cod_articulo),
);

Now, I have the tables "articulo" in node1 and node2.

As we see, I am doing REFERENCES to nodo2.proveedor and nodo2.articulo because my table "table2" is in this node "node2".

I gotta do reference to nodo1.proveedor when I am creating the table but I don't know how...

Can you help me?

Upvotes: 0

Views: 97

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

If "distributed database" means that you have two separate databases, you cannot create foreign key constraints in one database that references a table in another database.

You could create a materialized view in database 2 that pulls all the proveedor data from database 1 to database 2 and then create a foreign key constraint in database 2 that references the materialized view. Of course, since there would be a lag between when new data was written to the table on database 1 and when the materialized view was updated on database 2 that you could have windows where a child row couldn't be written despite the parent row existing on database 1. And if you deleted a row in database 1, you wouldn't find out whether there were child rows that would be orphaned until you tried to replicate that change to database 2. You'll need to write a lot of code to detect and to resolve these sorts of errors.

In Oracle, it would generally make far more sense to create a single database using RAC (Real Application Clusters) that is mounted on multiple physical servers. That would allow you to distribute the load across the database servers where each server has access to the full contents of the database rather than distributing subsets of data to different nodes.

Upvotes: 1

Related Questions