Tables with circular references in foreign key

Henry FRANQUET

Delphi Developer

Tue, 15 Jun 2004 23:10:01 GMT

Tables with circular references in foreign key

When migrating a base from Sybase to Interbase I face a problem of
circular reference in the design of the Sybase base
It seems that in Sybase one can define foreign key as controling the
data only when committing, so it is possible to have two tables, each
having a foreign key on the other.
How can we do in Interbase ? Is there a workaround ?

thanks

Claudio Valderrama C

Delphi Developer

Wed, 16 Jun 2004 12:03:03 GMT

Re:Tables with circular references in foreign key

Quote

"Henry FRANQUET" <henryfranq...@rillcom.com> wrote in message

news:3C2C8B48.3598AFEA@rillcom.com...

Quote

> When migrating a base from Sybase to Interbase I face a problem of
> circular reference in the design of the Sybase base
> It seems that in Sybase one can define foreign key as controling the
> data only when committing, so it is possible to have two tables, each
> having a foreign key on the other.
> How can we do in Interbase ? Is there a workaround ?

There are fields in system tables that support the DEFERRED mechanism, but
the logic is not implemented.
IMHO, circular references are a curse that should be avoided where possible.
You can use a FK and a trigger but it doesn't guarantee accuracy in
multi-user environments, as your transaction may miss uncommited records by
other transactions (and newer committed changes than the your txn starting
time, depending on the isolation).
http://www.cvalde.com/document/declaRefIntegVsTrig.htm

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase? WebRing

Henry FRANQUE

Delphi Developer

Tue, 22 Jun 2004 23:43:43 GMT

Re:Tables with circular references in foreign key

Thanks Claudio, but I can't change the application, so I have choice between
another application and interbase
or this application and sybase ?

"Claudio Valderrama C." a crit :

Quote

> "Henry FRANQUET" <henryfranq...@rillcom.com> wrote in message
> news:3C2C8B48.3598AFEA@rillcom.com...
> > When migrating a base from Sybase to Interbase I face a problem of
> > circular reference in the design of the Sybase base
> > It seems that in Sybase one can define foreign key as controling the
> > data only when committing, so it is possible to have two tables, each
> > having a foreign key on the other.
> > How can we do in Interbase ? Is there a workaround ?

> There are fields in system tables that support the DEFERRED mechanism, but
> the logic is not implemented.
> IMHO, circular references are a curse that should be avoided where possible.
> You can use a FK and a trigger but it doesn't guarantee accuracy in
> multi-user environments, as your transaction may miss uncommited records by
> other transactions (and newer committed changes than the your txn starting
> time, depending on the isolation).
> http://www.cvalde.com/document/declaRefIntegVsTrig.htm

> C.
> --
> Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
> Independent developer
> Owner of the Interbase? WebRing

Other Threads

1. How to update primary and foreign keys in referenced tables

2. Table reference of foreign key

3. Can't I add a foreign Key that references foreign Key of other Table?

4. Modify Key Field ( when it's foreign key in another table)

5. Foreign keys and multi table forms.

6. How to set foreign key to a table on run-time with PARADOX 7

7. FOREIGN KEY between diferent Tables on Diferent DB

8. alter table question for sql server 7 (changing foreign key)

9. adding a foreign key to a table

10. Retreive the table definition (foreign key...from the BDE)