“Insert if not exists” statement in SQLite

by

If you never want to have duplicates, you should declare this as a table constraint:

CREATE TABLE bookmarks(
    users_id INTEGER,
    lessoninfo_id INTEGER,
    UNIQUE(users_id, lessoninfo_id)
);

(A primary key over both columns would have the same effect.)

It is then possible to tell the database that you want to silently ignore records that would violate such a constraint:

INSERT OR IGNORE INTO bookmarks(users_id, lessoninfo_id) VALUES(123, 456)

More Related Contents:

  1. How do I DROP a constraint from a sqlite (3.6.21) table?
  2. SQLite Concurrent Access
  3. System.Data.SQLite Close() not releasing database file
  4. INSERT IF NOT EXISTS ELSE UPDATE?
  5. How to delete or add column in SQLITE?
  6. How to use ROW_NUMBER in sqlite
  7. Android – Pulling SQlite database android device
  8. ALTER COLUMN in sqlite
  9. Insert new column into table in sqlite?
  10. Efficient paging in SQLite with millions of records
  11. How do you enable LIMIT for DELETE in SQLite?
  12. SQLite Schema Information Metadata
  13. How to create custom functions in SQLite
  14. Sqlite group_concat ordering
  15. How to get a list of column names
  16. is there a limit to the size of a SQLite database?
  17. How to generate all dates between two dates
  18. Does it matter what extension is used for SQLite database files?
  19. How do I add a new column in between two columns?
  20. change sqlite file size after “DELETE FROM table”
  21. Are there any reserved words in SQLite?
  22. What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?
  23. Modify a Column’s Type in sqlite3
  24. Case sensitive and insensitive like in SQLite
  25. SQLite accent-insensitive search
  26. Will SQLite performance degrade if the database size is greater than 2 gigabytes?
  27. SQLite for large data sets?
  28. SQLite database supporting Unicode data
  29. Creating stored procedure in SQLite
  30. SQLITE3 VACUUM, “database or disk is full”

Leave a Comment