Partial Foreign Keys

[livius2]

As the #3750 is now in pull requests and will be soon available, i have moved my comment into new feature request for simpler processing.

Please add partial referential constraints (foreign keys).

Example:

---

instead of multiple "NOTES" tables NOTES_CUSTOMER, NOTES_PERSON, NOTES_THING ...

---

or one table with multiple ids which refere to other tables

CRATE TABLE NOTES
(
ID BIGINT NOT NULL PRIMARY KEY

, ID_CUSTOMER BIGINT NOT NULL
, ID_PERSON BIGINT NOT NULL
, ID_THING BIGINT NOT NULL
... other refids ...

DESCRIPTION VARCHAR(200)
);

---

we can do this:

CRATE TABLE NOTES
(
ID BIGINT NOT NULL PRIMARY KEY
, ID_REF BIGINT NOT NULL /* field from multiple tables */
,REF_TYPE INTEGER NOT NULL /* table type like Customer, Person, ... */
DESCRIPTION VARCHAR(200)
);

and now the crucial part - partial Foreign Keys:

ALTER TABLE NOTES ADD CONSTRAINT FK_NOTESCUSTOMER FOREIGN KEY(ID_REF) WHERE REF_TYPE=1 REFERENCES CUSTOMER(ID) ON DELETE NO ACTION ON UPDATE CASCADE; ALTER TABLE NOTES ADD CONSTRAINT FK_NOTES__PERSON FOREIGN KEY(ID_REF) WHERE REF_TYPE=2 REFERENCES PERSON(ID) ON DELETE NO ACTION ON UPDATE CASCADE; ALTER TABLE NOTES ADD CONSTRAINT FK_NOTESTHING FOREIGN KEY(ID_REF) WHERE REF_TYPE=3 REFERENCES THING(ID) ON DELETE NO ACTION ON UPDATE CASCADE;

This foreign keys will create partial index.

This is maybe not ideal design - but sometimes this will simplify many, many problems. In the past i have many times such situation that i need multiple "same" table only reference is different.

---

There is another case for this and the common one. It can exclude nulls from FK indexes.

ALTER TABLE TEST ADD CONSTRAINT FK_TEST__PERSON FOREIGN KEY(PERSON_ID) WHERE PERSON_ID IS NOT NULL REFERENCES PERSON(ID) ON DELETE NO ACTION ON UPDATE CASCADE;

[mrotteveel]

Are there any databases that already support this? What syntax do they use?

[livius2]

I do not know any. But as always new feature bring life from needs and concepts. This one i suppose have benefits.

Many times i must use overcomplicated structure only becaouse of not existing feature like this. I suppose other users was in the same situation.

[mrotteveel]

I think absence of such features in other DBMS is a good indication that we shouldn't implement it either, and presence of such feature could help us in determining syntax and applicable behaviour without reinventing the wheel.

[livius2]

The Firebird database is good, but being realistic, I know that it also has to make up a lot when it comes to functionality present in other databases Your statement proves it. However, don't be so strict with the Firebird. Sometimes other RDBMSs should also follow suit. This idea can be one of them, of course, if it is considered sensible and interesting enough to pave the way and implement it.

[livius2]

As a syntax i suppose the best and simpler to implement will be that WHERE part must be the last part of the Foreign Key definition. But you @mrotteveel you are better in sql conformance and syntax.

[gsbelarus]

As for me, it is more critical to have an ability to create FK without index. When referential integrity maintained by simple table scan. We have a lot of FK over tables with 10th of millions of records, which points to tiny references of 5-10 records. In addition, such FK fields could be mostly nulls. I have analyzed one of the cases and the size of the database of 300+GB could be reduced by healthy 60-70 GB if only there were an ability to create indexless FK.

[aafemt]

When referential integrity maintained by simple table scan.

You should try Oracle. Exclusive lock on whole table during DML most likely will make you to change your opinion.

[omachtandras]

When referential integrity maintained by simple table scan.

You should try Oracle. Exclusive lock on whole table during DML most likely will make you to change your opinion.

Yes, that's the real nightmare.

[hvlad]

When referential integrity maintained by simple table scan.

You should try Oracle. Exclusive lock on whole table during DML most likely will make you to change your opinion.

Why EX ? SH is enough. For almost never-changed lookup tables (as @gsbelarus speak about, I assume ) it makes no problem.

[aafemt]

AFAIU he was talking about no index on detail table. Indeed not so bad if master is read-only, but a disaster otherwise.

[hvlad]

AFAIU he was talking about no index on detail table.

Yes, and I still see no need in EX lock. It is enough to prevent change of detail table when master key changed and SH (PR) is what is needed..

Indeed not so bad if master is read-only, but a disaster otherwise.

The user should have ability to decide if FK needs and index or not. Also, it could be good if engine uses any suitable index without need to create dedicated one.

[gsbelarus]

The user should have ability to decide if FK needs and index or not. Also, it could be good if engine uses any suitable index without need to create dedicated one.

This is exactly what we need.

[livius2]

Today i am in same situation. Instead of creating 16 partial FK i must create 16 tables + generators + triggers to set generator values, PK, FK.... :cry: Life is hard :smirk:

[gsbelarus]

In real-life databases, what proportion are standalone indexes as compared to hidden indexes of Foreign Keys (FK) and Primary Keys (PK)? I believe the former accounts for less than 1%. This feature may not be significantly beneficial unless FB allows the selection of existing indexes for PK, FK, or provides a means to control index parameters during the creation of PK, FK.

[livius2]

In real-life databases, what proportion are standalone indexes as compared to hidden indexes of Foreign Keys (FK) and Primary Keys (PK)? I believe the former accounts for less than 1%. This feature may not be significantly beneficial unless FB allows the selection of existing indexes for PK, FK, or provides a means to control index parameters during the creation of PK, FK.

Hi. Can you point me about what you are talking about?

[livius2]

There is another case for this and the common one. It can exclude nulls from FK indexes.

ALTER TABLE TEST ADD CONSTRAINT FK_TEST__PERSON FOREIGN KEY(PERSON_ID) WHERE PERSON_ID IS NOT NULL REFERENCES PERSON(ID) ON DELETE NO ACTION ON UPDATE CASCADE;