omnilib/aiosqlite

No error message for foreign key violation.

AlphabetsAlphabets opened this issue · 2 comments

Description

It is as the title says.

Details

I have two tables. Player and History. The Challenger and Challenged column is a foreign key to the Player table's UID column.

When I run the following code it inserts random values for the Challenger and Challenged columns:

import aiosqlite
import asyncio


async def new_challenge(date: str, challenger: str, challenged: str):
    async with aiosqlite.connect("database.db") as db:
        await db.execute(
            "INSERT INTO History VALUES (?, ?, ?, ?)",
            (challenger, challenged, date, 0),
        )

if __name__ == "__main__":
    asyncio.run(new_challenge("date", "123", "456"))

Unexpectedly, there is no error message.

  • OS: Fedora
  • Python version: 3.11.5
  • aiosqlite version: 0.19.0
  • Can you repro on 'main' branch? I'm not sure what this means. I'll add this information in later as it gets clarified.
  • Can you repro in a clean virtualenv? Yes.
akx commented

Your repro code does not show your CREATE TABLE, so it's hard to use as stand-alone.

guacs commented

@AlphabetsAlphabets I don't think this is an issue with aiosqlite. sqlite doesn't enforce foreign key constraints by default as seen in the "Enabling Foreign Key Support" of the sqlite docs. So this should be working if you do the following:

import aiosqlite
import asyncio


async def new_challenge(date: str, challenger: str, challenged: str):
    async with aiosqlite.connect("database.db") as db:
        await db.execute("PRAGMA foreign_keys = ON")
        await db.execute(
            "INSERT INTO History VALUES (?, ?, ?, ?)",
            (challenger, challenged, date, 0),
        )

if __name__ == "__main__":
    asyncio.run(new_challenge("date", "123", "456"))