15

SQLite has this "feature" whereas even when you create a column of type INTEGER or REAL, it allows you to insert a string into it, even a string without numbers in it, like "the quick fox jumped over the lazy dog".

How do you prevent this kind of insertions to happen in your projects?

I mean, when my code has an error that leads to that kind of insertions or updates, I want the program to give an error, so I can debug it, not simply insert garbage in my database silently.

Tulains Córdova
  • 2,559
  • 2
  • 20
  • 33

2 Answers2

16

You can implement this using the CHECK constraint (see previous answer here). This would look like

   CREATE TABLE T (
       N   INTEGER CHECK(TYPEOF(N) = 'integer'),
       Str TEXT CHECK(TYPEOF(Str) = 'text'),
       Dt  DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL)
   );
Community
  • 1
  • 1
Seth
  • 2,683
  • 18
  • 18
1

The better and safer way is write a function (isNumeric, isString, etc) that validates the user input...