[Community feedback] Virtual foreign keys

[DavLagoHern]

This idea grew out of conversations with SQL users on Reddit about databases where important relationships exist in practice but were never declared as FOREIGN KEY constraints. I am opening it here to collect the different ways people document and navigate those relationships today.

The idea to explore

A virtual relationship would be stored only in LakeDB and would not modify the database. It might help navigation, autocomplete or QuerIA understand an intentionally documented link between columns.

The distinction from a real database constraint must stay obvious. A wrong virtual relationship could mislead a person and could be especially risky if it influenced AI-generated SQL, so validation and provenance would be essential.

Looking for real use cases

• How do you handle undocumented relationships today?
• Should virtual relations be used for table navigation?
• Should they influence autocomplete, QuerIA, both, or neither by default?
• How should LakeDB distinguish a real FK from a user-defined virtual relation?
• What should happen when columns disappear or a real FK is added later?

This is an exploration, not a planned feature. Reactions are useful, but detailed examples of the trust and maintenance problems are even more valuable.

[DavLagoHern]

Implemented in LakeDB Beta 5.4 as a deliberately bounded first version.

• Open View relationships from a MySQL/MariaDB database in Object Explorer.
• Real foreign keys use solid lines; LakeDB-only relationships use dashed lines and are labelled as local.
• Local relationships can be added, edited or removed without changing the database.
• Missing tables or columns are shown as stale.
• The map supports table dragging, canvas panning, wheel zoom, search, filters, and fit/reset controls.

The trust boundary remains narrow: local relationships do not affect autocomplete or QuerIA in this release, and custom table positions last only for the current open map.

Release and downloads: https://github.com/DavLagoHern/LakeDB/releases/tag/v1.0.0-beta.5.4

Follow-up ideas can remain here for navigation, autocomplete/QuerIA provenance, and conflict handling with future real foreign keys.