Tuesday, 13 May 2014

UNIQUE constraints in SQLite FTS (FTS3/FTS4) tables

The correct way to do this:
CREATE VIEW playlist_view AS SELECT * FROM playlist;

CREATE TRIGGER insert_playlist INSTEAD OF INSERT ON playlist_view
BEGIN
    SELECT RAISE(ABORT, 'column user_id is not unique') FROM playlist 
        WHERE user_id=new.user_id;
    INSERT INTO playlist (from_user, from_id, created_time, 
                          created_time_formated, user_id) 
    VALUES (NEW.from_user, NEW.from_id, NEW.created_time, 
            NEW.created_time_formated, NEW.user_id);
END;

-- And you do the insertion on the view 
INSERT INTO playlist_view (from_user,from_id,created_time,created_time_formated,user_id) SELECT ...;