V2.0-alpha.20180129 foreign key column requires index

I completely understand that de id there the child refers to should be a primary key and requires an index. For the foreign key columns I am not so sure.
ALTER TABLE
pq: foreign key requires an existing index on columns (“templateid”)
Error: pq: foreign key requires an existing index on columns (“templateid”)

ALTER TABLE ONLY hosts ADD CONSTRAINT c_hosts_3 FOREIGN KEY (templateid) REFERENCES hosts (hostid) ON DELETE CASCADE;

having an index on this column could be a performance improvement since it can prevent a table lock but for tables that are not update very often it is not needed to have an index for the foreign key.

Can this requirement be lifted, or is this due to the distributed nature of crdb? In Oracle and postgres it is not needed …

Dear Ronald,

our design for foreign keys is documented here:

The specific explanation as to why we require an index on both the referencing and referenced columns is outlined here:

Values in a referenced column must be unique, otherwise it would be ambiguous which instance of a duplicated value was the one referenced and thus subject to the foreign key restrictions. In practice, uniqueness enforcement is implemented via an index, so the requirement of an index on the referenced column was already implied by the uniqueness requirement.

Allowing the referencing column to be unindexed has obvious performance drawbacks (operations on the referenced table would need to perform table-scans).

In the latter case (motivation for an index on the referencing column) what is important is to notice what happens when a row in the referenced table is modified or deleted. The FK relationships has to find in the referencing table if there is a row that uses the modified/deleted row to validate the FK constraint. To find this requires an index.

Hence the two requirements:

  • a unique index on the referenced column(s)
  • a non-unique index on the referencing column

Conceptually, we could drop the 2nd requirement, but that would require 1) more complexity in CockroachDB to handle this case 2) tremendous lookup costs whenever a referenced row is modified or deleted.

The combination of these two reasons explains why we are not yet planning to lift this requirement.

Eventually we may consider doing so if e.g. a user or customer provides us a compelling use case where the referenced table is append-only (e.g. a log) and the overhead of maintaining the referencing index is hampering performance with no benefit.

Does this answer your question?

Hi Raphael,

thanks for the answer. I think this is a pity since this differs from postgres (and oracle) where we have he choice to make that index or to leave it. And yes, it could have a performance impact. Some tools generate an index for every referencing column, I know but the designer has the better insight in when it is really beneficial and when not.
I still hope for a reconsideration.

Hi Ronald

thank you for your reaction. I have created a Github issue to track your request: https://github.com/cockroachdb/cockroach/issues/22253

Feel free to add any additional arguments there so we can prioritize the work effectively.

Thank you for bringing this to our attention!