While connecting with DBeaver foreign key relationship is not shown in ER diagram

[fuubarbaz]

What happens?

I'm trying to get rid of sqlite and replace it with duckdb. One of the key requirement I have is smooth integration with dbeaver.
Seems like the current version of dbeaver (23.2.3) doesn't show constraints correctly for duckdb. I mean foreign key relationship is not correctly show in ER Diagram.
I hope this is the right place to create this particular issue.

Ideally it should be like below (screen shot from sqlite)

To Reproduce

• Install and connect dbeaver with duckdb as mentioned in the page https://duckdb.org/docs/guides/sql_editors/dbeaver
• Right click on the connection and create new sql script.
• Paste the following
  CREATE TABLE students(id INTEGER PRIMARY KEY, name VARCHAR); CREATE TABLE exams(exam_id INTEGER REFERENCES students(id), grade INTEGER); INSERT INTO students VALUES (2, 'Student 2'); INSERT INTO exams VALUES (2, 20);
• Expand database and then right click on main and select View Diagram.

OS:

Windows

DuckDB Version:

0.91

DuckDB Client:

0.91

Full Name:

Vivek Kumar Jain

Affiliation:

ASML

Have you tried this on the latest main branch?

I have tested with a release build (and could not test with a main build)

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[LonwoLonwo]

As I can see, it is not supported by the driver:

java.sql.SQLFeatureNotSupportedException: getImportedKeys
	at org.duckdb.DuckDBDatabaseMetaData.getImportedKeys(DuckDBDatabaseMetaData.java:947)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCDatabaseMetaDataImpl.getImportedKeys(JDBCDatabaseMetaDataImpl.java:1206)
	at org.jkiss.dbeaver.ext.generic.model.meta.GenericMetaModel.prepareForeignKeysLoadStatement(GenericMetaModel.java:765)
	at org.jkiss.dbeaver.ext.generic.model.ForeignKeysCache.prepareObjectsStatement(ForeignKeysCache.java:78)
	at org.jkiss.dbeaver.ext.generic.model.ForeignKeysCache.prepareObjectsStatement(ForeignKeysCache.java:1)
	at org.jkiss.dbeaver.model.impl.jdbc.cache.JDBCCompositeCache.loadObjects(JDBCCompositeCache.java:298)

Does DuckDB have plans to support foreign key info for the driver side? The duckdb_constraints contains some necessary info.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[rsmckinney]

This is a rather large hole in the driver. Are there plans to implement this? If not, would you accept an external PR?

[Mause]

External PRs are always welcome

[wyhaya]

Are we just missing a method to get a list of foreign keys in DuckDB?

-- SQLite
SELECT * FROM pragma_foreign_key_list("...");
-- MySQL
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE referenced_column_name IS NOT NULL;
-- SQL Server
SELECT * FROM sys.foreign_key_columns;

-- DuckDB
???

[rsmckinney]

-- DuckDB (as far as I know)
SELECT * FROM duckdb_constraints WHERE constraint_type = 'FOREIGN KEY' . . .

But the results don't provide PK linkage, instead it just dumps the FK ddl sql. I have an implementation of this method that parses the FK sql to produce the PK linkage. It's not that complicated and is working fine.

I'd like to make a pull request for duckdb-java, however that project is hard to deal with, it's not really setup for actual Java dev. For now I have one-off implementations like this one where the duckdb JDBC driver fails... there are too many of these to count.

[bskubi]

I am also having this issue. Hope a fix is forthcoming! Thanks.

[lukaseder]

This could be implemented simply with the following query:

SELECT
  pk_tc.table_catalog AS PKTABLE_CAT,
  pk_tc.table_schema AS PKTABLE_SCHEM,
  pk_tc.table_name AS PKTABLE_NAME,
  pk_kcu.column_name AS PKCOLUMN_NAME,
  fk_tc.table_catalog AS FKTABLE_CAT,
  fk_tc.table_schema AS FKTABLE_SCHEM,
  fk_tc.table_name AS FKTABLE_NAME,
  fk_kcu.column_name AS FKCOLUMN_NAME,
  fk_kcu.ordinal_position AS KEY_SEQ,
  CASE rc.update_rule 
    WHEN 'CASCADE' THEN 0
    WHEN 'RESTRICT' THEN 1
    WHEN 'SET NULL' THEN 2
    WHEN 'SET DEFAULT' THEN 4
    ELSE 3
  END AS UPDATE_RULE,
  CASE rc.delete_rule
    WHEN 'CASCADE' THEN 0
    WHEN 'RESTRICT' THEN 1
    WHEN 'SET NULL' THEN 2
    WHEN 'SET DEFAULT' THEN 4
    ELSE 3
  END AS DELETE_RULE,
  rc.constraint_name AS FK_NAME,
  rc.unique_constraint_name AS PK_NAME,
  CASE 
    WHEN fk_tc.is_deferrable = 'YES' AND fk_tc.initially_deferred = 'YES' THEN 5
    WHEN fk_tc.is_deferrable = 'YES' AND fk_tc.initially_deferred = 'NO' THEN 6
    ELSE 7
  END AS DEFERRABILITY
FROM information_schema.referential_constraints rc
  JOIN information_schema.table_constraints fk_tc
    ON fk_tc.constraint_catalog = rc.constraint_catalog
    AND fk_tc.constraint_schema = rc.constraint_schema 
    AND fk_tc.constraint_name = rc.constraint_name
  JOIN information_schema.key_column_usage fk_kcu
    ON fk_kcu.constraint_catalog = rc.constraint_catalog
    AND fk_kcu.constraint_schema = rc.constraint_schema
    AND fk_kcu.constraint_name = rc.constraint_name
  JOIN information_schema.table_constraints pk_tc
    ON pk_tc.constraint_catalog = rc.unique_constraint_catalog
    AND pk_tc.constraint_schema = rc.unique_constraint_schema
    AND pk_tc.constraint_name = rc.unique_constraint_name
  JOIN information_schema.key_column_usage pk_kcu
    ON pk_kcu.constraint_catalog = pk_tc.constraint_catalog
    AND pk_kcu.constraint_schema = pk_tc.constraint_schema
    AND pk_kcu.constraint_name = pk_tc.constraint_name
    AND pk_kcu.ordinal_position = fk_kcu.ordinal_position
    -- AND pk_kcu.ordinal_position = fk_kcu.position_in_unique_constraint
ORDER BY 
  FKTABLE_CAT, 
  FKTABLE_SCHEM, 
  FKTABLE_NAME, 
  KEY_SEQ

Note that in the last JOIN predicate, instead of fk_kcu.ordinal_position, it would be better to use fk_kcu.position_in_unique_constraint, though this currently isn't possible due to this bug:

• Wrong content in INFORMATION_SCHEMA.KEY_COLUMN_USAGE.POSITION_IN_UNIQUE_CONSTRAINT duckdb#20247

I suspect fk_kcu.ordinal_position is fine for now as I believe it's not possible to reference unique constraint columns out of order in DuckDB.