Incorrect Error Message When Insert Violates Foreign Key

[gmoore016]

What happens?

When inserting from a dataframe into a table with a foreign key and the foreign key constraint is violated, the package attempts to tell you which row violates the constraint. However, it seems to be instead pulling the key from the first row of the dataframe, not the row that's actually violating the constraint.

To Reproduce

Using Pandas 2.3.3:

import pandas as pd
import duckdb

con = duckdb.connect("~/test.db")

con.execute("""
    CREATE TABLE reference (
        reference_id INT PRIMARY KEY
    );
""")

ref_frame = pd.DataFrame({"reference_id": [1, 2]})

con.execute("""
    INSERT INTO reference BY NAME SELECT * FROM ref_frame;
""")

con.execute("""
    CREATE TABLE data (
        data_id INT PRIMARY KEY,
        reference_id INT,
        FOREIGN KEY (reference_id) REFERENCES reference(reference_id)
    );
""")

data_frame = pd.DataFrame({"data_id": [1, 2], "reference_id": [1, 3]})

con.execute("""
    INSERT INTO data BY NAME SELECT * FROM data_frame;
""")

This yields the following error:

_duckdb.ConstraintException: Constraint Error: Violates foreign key constraint because key "reference_id: 1" does not exist in the referenced table

However, clearly reference_id 1 exists; the problem is reference_id 3.

OS:

Ubuntu 22.04

DuckDB Package Version:

1.4.2

Python Version:

3.11.3

Full Name:

Gideon Moore

Affiliation:

Stanford University, Economics Department

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?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[paultiq]

Reproduces with CLI (removing python & pandas) on 1.4.0+.

Looks like a regression, since 1.3.2 correctly says:

duckdb.duckdb.ConstraintException: Constraint Error: Violates foreign key constraint because key "reference_id: 3" does not exist in the referenced table

CREATE TABLE reference (
        reference_id BIGINT PRIMARY KEY
    );
    INSERT INTO reference values (1), (2);
    CREATE TABLE data (
        data_id INT PRIMARY KEY,
        reference_id BIGINT,
        FOREIGN KEY (reference_id) REFERENCES reference(reference_id)
    );
    create table temp_data as select * from (values(1, 1), (2, 3)) as t(data_id, reference_id);
    INSERT INTO data BY NAME SELECT * FROM temp_data;

DuckDB v1.4.2 (Andium) 68d7555f68
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
...
Constraint Error:
Violates foreign key constraint because key "reference_id: 1" does not exist in the referenced table
D

[gmoore016]

Given @paultiq's MWE in the DuckDB CLI, I've opened a new issue (linked above) in the primary repository rather than the Python package. Will close this one since it's not Python-exclusive.