Foreign keys in system tables

[Dimitry Sibiryakov]

Hello All,

it looks like foreign keys have duplicate definition: one in
RDB$REF_CONSTRAINTS and another in RDB$INDICES.RDB$FOREIGN_KEY.
Are they both reliable?

--
WBR, SD.

[Mark Rotteveel]

What do you mean with duplicate? They should both exist for a foreign
key to be valid.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 09.11.2023 9:56:
> What do you mean with duplicate? They should both exist for a foreign key to be
> valid.

No, one can be missed without breaking metadata integrity.
A typical task: to find PK index for a FK constraint can be solved in two ways:

1) Constraint -> ref_constraint -> constraint -> index
2) Constraint -> index -> index.

My question is: can I rely on second way to work?

It will be broken if ever constraints without supporting index are
implemented but I'm asking only about current situation.

--
WBR, SD.

[Mark Rotteveel]

On 09-11-2023 11:38, 'Dimitry Sibiryakov' via firebird-devel wrote:
> 'Mark Rotteveel' via firebird-devel wrote 09.11.2023 9:56:
>> What do you mean with duplicate? They should both exist for a foreign
>> key to be valid.
>
>   No, one can be missed without breaking metadata integrity.

That it *can* be missing sounds like a bug to me (assuming that it
really can be missing, and not just an assumption on your part), and
even if it *can*, it *shouldn't* be missing.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 09.11.2023 12:51:
>>    No, one can be missed without breaking metadata integrity.
>
> That it *can* be missing sounds like a bug to me (assuming that it really can be
> missing, and not just an assumption on your part), and even if it *can*, it
> *shouldn't* be missing.

Here when I say "missed" I mean rather "absent". From DB design POV
duplicated relationship between entities is a flaw in normalization.
AFAIU RDB$INDICES.RDB$FOREIGN_KEY is an ancient artefact from times when
there was no explicit constraints so I wonder if this field is still actively
used and can be relied to.

--
WBR, SD.