[adrianmsmith]

> But do schema-less implement foreign constraints at all????

In MySQL 5.7 you can have tables with JSON columns (like PostgreSQL's JSONB), and also "Generated columns" which are expressions over other columns (e.g. extract a value from the JSON). You can then use those generated columns as the source or destination of foreign key constraints.

[SmellTheGlove]

Neat, I didn't know that. Is that more or less just having FK constraints on a view, though, or am I missing something conceptually here?

[adrianmsmith]

Right, "Computed Columns" are "view-like" columns added to normal tables. So you define your table with a few normal columns, and a few generated columns (which are each expressions over the normal columns, or over other generated columns).

On those "generated columns" you can create indexes, reference other tables (foreign keys) or be referenced by other tables (foreign keys).

MySQL has a binary JSON data type (like most databases) and also expressions to extract data from those JSON fields.

MySQL has a lot of disadvantages vs PostgreSQL, but having foreign keys on elements within JSON (via this mechanism) is a thing MySQL can do that PostgreSQL can't :) (Unless I'm mistaken?)

[MarHoff]

I think you are mistaken, even if implementation will vary you can have the same behavior (However I won't ever try in production because jsonb base FK is a pretty scary concept IMHO)

However on a solution I've implemented on PostgreSQL I expand jsonb as regular field in a view and made that view editable via trigger. This way users can update jsonb_field without being aware of because they edit it through a view as if they are normal fields.