SQLite: Handling Unique Constraints on Composite Keys with NULL Values
I can't seem to get I'm working on a personal project and I'm getting frustrated with I'm working with an scenario in SQLite where I have a table with a composite primary key that involves two columns, one of which can hold NULL values. The table is defined as follows: ```sql CREATE TABLE my_table ( col1 INTEGER, col2 TEXT, col3 TEXT, PRIMARY KEY (col1, col2) ); ``` The goal is to allow multiple entries with NULL in `col2`, but still enforce uniqueness for combinations where `col2` is not NULL. However, when I try to insert records like this: ```sql INSERT INTO my_table (col1, col2, col3) VALUES (1, NULL, 'A'); INSERT INTO my_table (col1, col2, col3) VALUES (1, NULL, 'B'); INSERT INTO my_table (col1, col2, col3) VALUES (1, 'NotNull', 'C'); INSERT INTO my_table (col1, col2, col3) VALUES (1, 'NotNull', 'D'); ``` I get an behavior on the second insert for the NULL value: ``` UNIQUE constraint failed: my_table.rowid ``` I thought that SQLite treats NULLs as distinct values in a unique constraint. I've also looked into using a partial index as a potential solution, but I'm not sure how to implement it correctly for this case. Here’s what I considered: ```sql CREATE UNIQUE INDEX idx_col1_col2 ON my_table (col1, col2) WHERE col2 IS NOT NULL; ``` I've tried this but it didn't resolve the scenario with the inserts. Is there a best practice for handling this kind of scenario in SQLite, or is there something I might be overlooking? Any guidance on how to structure my table or the index would be greatly appreciated. For context: I'm using Sql on Linux. I'm open to any suggestions. I'm working with Sql in a Docker container on Ubuntu 20.04. I'm open to any suggestions. I recently upgraded to Sql LTS.