Foreign key referencing a non-primary field results in `ValueError: foreign key mismatch` when using `ON CONFLICT`

[oliwoli]

I am getting ValueError: foreign key mismatch when inserting data with ON CONFLICT whenever I have a foreign key referencing a key which is not the primary key.

I was able to reproduce it on Fedora Linux with Python and Beekeeper Studio as well as online with Outerbase. The code below works with sqlite3 (Python) but fails with libsql.

Steps to reproduce:

1. Create a schema with two tables like this:

   
   CREATE TABLE "authors"( 
   id TEXT NOT NULL,
   username TEXT NOT NULL,
   PRIMARY KEY ("id")
   ) STRICT;

CREATE TABLE "books"( book_id INTEGER NOT NULL, author_username TEXT, PRIMARY KEY ("book_id"), FOREIGN KEY("author_username") REFERENCES "authors"("username") ) STRICT;

2. Insert into the `authors` table using `ON CONFLICT`.
_(I also tested inserting into the "books" table the same way and got the same error.)_
```sql
INSERT INTO authors (
    id,
    username
)
VALUES (
    'some_id',
    'johndoe'
)
ON CONFLICT (id) DO UPDATE SET
    username = excluded.username;

4. Output: Python: ValueError: foreign key mismatch - "books" referencing "authors" Outerbase: SQLITE_UNKNOWN: SQLite error: foreign key mismatch - "books" referencing "authors"

Here is my Python script I've used for testing:

```python import sqlite3 import libsql_experimental as libsql ## works with sqlite3, but not with libsql #conn = sqlite3.connect(":memory:") conn = libsql.connect(":memory:") cursor = conn.cursor() schema = """ CREATE TABLE "authors"( id TEXT NOT NULL, username TEXT NOT NULL, PRIMARY KEY ("id") ) STRICT; CREATE TABLE "books"( book_id INTEGER NOT NULL, author_username TEXT, PRIMARY KEY ("book_id"), FOREIGN KEY("author_username") REFERENCES "authors"("username") ) STRICT; """ cursor.executescript(schema) upsert_query = """ INSERT INTO authors ( id, username ) VALUES ( 'some_id', 'johndoe' ) ON CONFLICT (id) DO UPDATE SET username = excluded.username; """ # Execute the upsert query cursor.execute(upsert_query) conn.commit() print("Successfully inserted data into authors table") ```

[athoscouto]

Hi @oliwoli, I've replied on Discord but adding my findings here as well.

This is reproducible on SQLite when PRAGMA FOREIGN_KEYS = ON;. From SQLite docs, looks like the problem is that you have a FK to a column that is not unique.

By changing your definition of authors table to:

CREATE TABLE "authors"( 
    id TEXT NOT NULL,
    username TEXT NOT NULL UNIQUE,
    PRIMARY KEY ("id")
) STRICT;

The issue is not reproducible.

I'm going to close this one, feel free to reopen it if you think it isn't addressed.