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 ...;

Comments