Skip to content

Latest commit

 

History

History
35 lines (22 loc) · 2.1 KB

tips.md

File metadata and controls

35 lines (22 loc) · 2.1 KB
Raw

Helpful tips for SQLite3

Creating good tables

It's a good idea to use INTEGER PRIMARY KEY AUTOINCREMENT as one of the columns in a table. This ensures two things:

  • INTEGER PRIMARY KEY: improved performance by reusing SQLite3's built-in rowid column.
  • AUTOINCREMENT: no future row will have the same ID as an old one that was deleted. This can prevent potential bugs and security breaches.

If you don't use INTEGER PRIMARY KEY, then you must use NOT NULL in all of your your primary key columns. Otherwise you'll be victim to an SQLite3 bug that allows primary keys to be NULL.

Any column with INTEGER PRIMARY KEY will automatically increment when setting its value to NULL. But without AUTOINCREMENT, the behavior only ensures uniqueness from currently existing rows.

It should be noted that NULL values count as unique from each other. This has implications when using the UNIQUE contraint or any other equality test.

Default values

When a column has a DEFAULT value, it only gets applied when no value is specified for an INSERT statement. If the INSERT statement specifies a NULL value, the DEFAULT value is NOT used.

Foreign keys

Foreign key constraints are not enforced if the child's column value is NULL. To ensure that a relationship is always enforced, use NOT NULL on the child column.

Example:

CREATE TABLE comments (value TEXT, user_id INTEGER NOT NULL REFERENCES users);

Foreign key clauses can be followed by ON DELETE and/or ON UPDATE, with the following possible values:

  • SET NULL: if the parent column is deleted or updated, the child column becomes NULL.
    • NOTE: This still causes a constraint violation if the child column has NOT NULL.
  • SET DEFAULT: if the parent column is updated or deleted, the child column becomes its DEFAULT value.
    • NOTE: This still causes a constraint violation if the child column's DEFAULT value does not correspond with an actual parent row.
  • CASCADE: if the parent row is deleted, the child row is deleted; if the parent column is updated, the new value is propagated to the child column.