If you create a table with a foreign key it isn’t available to tools that would normally be able to interrogate the link to determine relationships e.g.
- DBeaver schema diagrams or Foreign Key lists.
- SQLAlchemy (python) - can’t auto join through foreign keys. Would expect to write:
query(Title.decription).join(User).filter(User.email == 'firstname.lastname@example.org')
But need to write:
query(Title.decription).join(User, Title.id == User.id_title).filter(User.email == 'email@example.com')
I initially discovered this was that SQLAlchemy doesn’t even include the foreign keys in the table DDL under the CDB dialect (issue raised) but applying DDL generated for postgreSQL and running that on CDB does result in enforced foreign keys as we would expect.
Under DBeaver the error raised when attempting to list foreign keys is:
SQL Error [XX000]: ERROR: syntax error at or near "." SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, (i.keys).n AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary, information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i) i ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE true AND n.nspname = 'caas' AND ct.relname = 'title_dict' AND i.indisprimary ORDER BY table_name, pk_name, key_seq ^
Can anyone comment on plans to improve compatibility to this level or are these unlikely to be solved?