Limit an sqlite Table’s Maximum Number of Rows

by

Another solution is to precreate 100 rows and instead of INSERT use UPDATE to update the oldest row.
Assuming that the table has a datetime field, the query

UPDATE ...
WHERE datetime = (SELECT min(datetime) FROM logtable)

can do the job.

Edit: display the last 100 entries

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

Update: here is a way to create 130 “dummy” rows by using join operation:

CREATE TABLE logtable (time TIMESTAMP, msg TEXT);
INSERT INTO logtable DEFAULT VALUES;
INSERT INTO logtable DEFAULT VALUES;
-- insert 2^7 = 128 rows
INSERT INTO logtable SELECT NULL, NULL FROM logtable, logtable, logtable,
   logtable, logtable, logtable, logtable;
UPDATE logtable SET time = DATETIME('now');

More Related Contents:

  1. Version of SQLite used in Android?
  2. Database development mistakes made by application developers [closed]
  3. What are the performance characteristics of sqlite with very large database files? [closed]
  4. What’s wrong with foreign keys?
  5. Surrogate vs. natural/business keys [closed]
  6. What are database normal forms and can you give examples? [closed]
  7. NoSql vs Relational database
  8. How to recover a corrupt SQLite3 database?
  9. Multiple schemas versus enormous tables [closed]
  10. What are the design criteria for primary keys?
  11. Techniques for database inheritance?
  12. What is the most efficient way to store tags in a database?
  13. SQLite UPSERT / UPDATE OR INSERT
  14. How to backup sqlite database?
  15. Microservices with shared database? using multiple ORM’s? [closed]
  16. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  17. How should international geographical addresses be stored in a relational database?
  18. Still Confused About Identifying vs. Non-Identifying Relationships
  19. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  20. Ideas on database design for capturing audit trails [closed]
  21. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  22. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  23. Versioning Database Persisted Objects, How would you? [closed]
  24. Sqlite in flutter, how database assets work
  25. Best design for a changelog / auditing database table? [closed]
  26. Android: Where are database files stored?
  27. Populating Spinner From SQLite Database Android
  28. Need help in developing DB logic
  29. How to find SQLITE database file version
  30. Database – Data Versioning [closed]

Leave a Comment