I've never heard of DuckDB. I haven't used SQLite a lot, but one thing that I noticed it lacks, and this is pretty important, is that you can't add a foreign key constraint after a table is created, which is bad if you change your schema later. I don't even understand why this limitation exists.
One typical way to work around this limitation is to create a new table, copy the data there, and drop the old table. Copying from table to table is in general very fast.
Primary keys and foreign keys are rarely used in data science workloads. In our performance guide, we recommend users to avoid using primary keys unless they are absolutely necessary [1]. In workloads running in infrastructure components, keys and constraints are more important, and they are supported by DuckDB (but currently some of them can only be added when the table is created).
