Impossible to export sqlite to duckdb keeping constraints. "information_schema.table_constraints" waiting for a memory use.

[Victordeleusse]

What happens?

I try to ATTACH a sqlite db but impossible to export constraints as primary and foreign keys.

To Reproduce

Working on an attached sqlite database :

con = duckdb.connect()
con.execute("ATTACH 'california_schools.sqlite' as cs (TYPE sqlite)")
con.execute("USE cs")
res = con.execute("SELECT * FROM information_schema.table_constraints").fetchall()
print(res)

Here an error occured

CatalogException                          Traceback (most recent call last)

Cell In[187], line 1

----> 1 res = con.execute("SELECT * FROM information_schema.table_constraints").fetchall()

      2 print(res)

 

CatalogException: Catalog Error: Table with name table_constraints does not exist!

Did you mean "memory.information_schema.table_constraints"?

LINE 1: SELECT * FROM information_schema.table_constraints

When adding it and using it as requiered, the return list is empty whereas constraints keys well exist in database, so it seems that the constraints are not export from sqlite to DuckDB (as the command ALTER TABLE is still not functionnal in DuckDB it seems make sens). So how to face off this issue ? And how to allow exporting an sqlite db to a duckdb one keeping constraints and metadata ?

Thank you.

OS:

x86_64

DuckDB Version:

latest

DuckDB Client:

Python

Hardware:

No response

Full Name:

Victor de Leusse

Affiliation:

42

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[szarnyasg]

Thanks! Here's a plain SQL reproducer on sakila.db:

ATTACH 'sakila.db' as cs (TYPE sqlite);
USE cs;
SELECT * FROM information_schema.table_constraints;

[Mytherin]

Thanks for the report!

It looks like this has been fixed in the latest dev version:

D LOAD 'build/debug/extension/sqlite_scanner/sqlite_scanner.duckdb_extension';
D ATTACH 'sqlite:data/db/sakila.db' AS cs;
D USE cs;

D SELECT * FROM information_schema.table_constraints LIMIT 5;
┌────────────────────┬───────────────────┬──────────────────────┬───────────────┬───┬─────────────────┬───────────────┬────────────────────┬──────────┬────────────────┐
│ constraint_catalog │ constraint_schema │   constraint_name    │ table_catalog │ … │ constraint_type │ is_deferrable │ initially_deferred │ enforced │ nulls_distinct │
│      varchar       │      varchar      │       varchar        │    varchar    │   │     varchar     │    varchar    │      varchar       │ varchar  │    varchar     │
├────────────────────┼───────────────────┼──────────────────────┼───────────────┼───┼─────────────────┼───────────────┼────────────────────┼──────────┼────────────────┤
│ cs                 │ main              │ actor_actor_id_not…  │ cs            │ … │ CHECK           │ NO            │ NO                 │ YES      │ YES            │
│ cs                 │ main              │ actor_first_name_n…  │ cs            │ … │ CHECK           │ NO            │ NO                 │ YES      │ YES            │
│ cs                 │ main              │ actor_last_name_no…  │ cs            │ … │ CHECK           │ NO            │ NO                 │ YES      │ YES            │
│ cs                 │ main              │ actor_last_update_…  │ cs            │ … │ CHECK           │ NO            │ NO                 │ YES      │ YES            │
│ cs                 │ main              │ actor_actor_id_pkey  │ cs            │ … │ PRIMARY KEY     │ NO            │ NO                 │ YES      │ YES            │
├────────────────────┴───────────────────┴──────────────────────┴───────────────┴───┴─────────────────┴───────────────┴────────────────────┴──────────┴────────────────┤
│ 5 rows                                                                                                                                          11 columns (9 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘