Column constraints are not replicated from SQLite table

[maruth-stripe]

What happens?

For a simple table generated with SQLite

sqlite> .open example.db
sqlite> .schema
CREATE TABLE Example(foo INTEGER PRIMARY KEY);

On loading with DuckDB the PRIMARY KEY constraint on the foo column is not pulled in

D CALL sqlite_attach("example.db");
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows  │
└─────────┘
D DESCRIBE Example
> ;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐
│ column_name │ column_type │  null   │   key   │ default │ extra │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ int32 │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤
│ foo         │ BIGINT      │ YES     │         │         │       │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴───────┘

To Reproduce

To generate the SQLite table

CREATE TABLE Example(foo INTEGER PRIMARY KEY);

To load in DuckDB

CALL sqlite_attach("example.db");
DESCRIBE Example;

OS:

macOS

SQLite Version:

3.39.5

DuckDB Version:

v0.8.1

DuckDB Client:

CLI

Full Name:

Maruth Goyal

Affiliation:

Stripe

Have you tried this on the latest master branch?

• [X] I agree

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

• [X] I agree

[gladkikhartem]

Why would you expect to have constraints? Constraints are for writing, and this is a "scanner" extension.

[Mytherin]

Thanks for the report! It is indeed not expected that DESCRIBE shows the column constraints when using sqlite_attach, as that creates only a set of views (which do not have constraints).

It is however expected that ATTACH shows the column constraints - and it appears that that is not working correctly currently either. This appears to be a problem with how the upstream duckdb_constraints function handles constraints in custom tables and not a problem specifically with this extension, however.