Maintaining relational integrity between 2 databases
Hi,
Is it possible to maintain relational integrity between 2 databases
with usages of a serial?
Example:
Database 1: foo
Create table Company (
company_id serial,
company_name char(128),
PRMARY KEY (company_id)
);
Database 2: bar
Create table address (
address_id serial,
adres char(128),
city char(128),
state char(2),
company_id int,
PRIMARY KEY (address_id),
FOREIGN KEY (company_id) REFERENCES foo.company
);
If it's not possible, what's the way to do this?
John Leerentveld<jl@ibt.nl>
HI Buddy,
Your problem is not because of that the primary key column is serial.
The problem is that the 2 tables are in separate databases. If you need
a referential constraint between the 2 tables then the tables belong in
same database. I don't know why you kept them in separate databases. I
am not aware of any RDBMS which implements referential constraint across
databases.
Thanks,
Khem Chander
kchande@yahoo.com
John Leerentveld wrote:
> Hi,
>
> Is it possible to maintain relational integrity between 2 databases
> with usages of a serial?
>
> Example:
>
> Database 1: foo
>
> Create table Company (
> company_id serial,
> company_name char(128),
> PRMARY KEY (company_id)
> );>
> Database 2: bar
>
> Create table address (
> address_id serial,
> adres char(128),
> city char(128),
> state char(2),
> company_id int,
> PRIMARY KEY (address_id),
> FOREIGN KEY (company_id) REFERENCES foo.company
> );>
> If it's not possible, what's the way to do this?
>
> John Leerentveld<jl@ibt.nl>
You will have to
1) implement a 'minicompany' of the company table from database A in
database B with insert trigger
2) link the related tables from database B to this 'minicompany'
2) maintain integrity between 'company' and 'minicompany' with delete
trigger (and update trigger if you allow update of PK)
database B
1) create minipresence
create table minicompany (
company_id integer,
primary key(company_id)
)2) link adress to this minicompany
FOREIGN KEY (company_id) REFERENCES minicompany
database A
1) insert trigger propagates object to other database
create trigger ti_company
insert on company
referencing new as nieuw
for each row (insert into b:minicompany(company_id)
values(nieuw.company_id))1) delete trigger first deletes object in other database !!! if there are no
child records !!!
create trigger td_company
delete on company
referencing old as oud
for each row (delete from b:minicompany where company_id
oud.company_id)
Khem Chander wrote:
> HI Buddy,
> Your problem is not because of that the primary key column is serial.
> The problem is that the 2 tables are in separate databases. If you need
> a referential constraint between the 2 tables then the tables belong in
> same database. I don't know why you kept them in separate databases. I
> am not aware of any RDBMS which implements referential constraint across
> databases.
>
> Thanks,
> Khem Chander
> kchande@yahoo.com
>
> John Leerentveld wrote:
>
> > Hi,
> >
> > Is it possible to maintain relational integrity between 2 databases
> > with usages of a serial?
> >
> > Example:
> >
> > Database 1: foo
> >
> > Create table Company (
> > company_id serial,
> > company_name char(128),
> > PRMARY KEY (company_id)
> > );> >
> > Database 2: bar
> >
> > Create table address (
> > address_id serial,
> > adres char(128),
> > city char(128),
> > state char(2),
> > company_id int,
> > PRIMARY KEY (address_id),
> > FOREIGN KEY (company_id) REFERENCES foo.company
> > );> >
> > If it's not possible, what's the way to do this?
> >
> > John Leerentveld<jl@ibt.nl>