Creating column constraint to allow only digits
(1) By MDR (achab61) on 2022-03-13 18:26:41 [link] [source]
Hi all,
I'm newbie on SQLite and I have a basic question about how to create a constraint on a column to restrict the input to numbers only.
Thanks for any hints
(2) By SeverKetor on 2022-03-13 19:14:01 in reply to 1 [link] [source]
This is kind of ill-defined. Do you have an integer column? Either put STRICT on the table or CHECK (typeof(Colname) IS 'integer').
(3) By Harald Hanche-Olsen (hanche) on 2022-03-13 19:17:54 in reply to 1 [link] [source]
If you want to accept just integers, use check(typeof(x)='integer'), replacing x with the actual variable name.
If you want the column to accept real numbers as well, you'll have to add OR typeof(x)='real').
If you want the column to accept strings consisting of decimal digits only, I don't see a way to do that with stock SQLite. But why would you want to do that, unless you want to allow significant leading zeros (because your data is not actually numbers at all, but strings of digits, like phone numbers and the like)? Or digit strings too long to correspond to an integer that SQLite can store as such. Sorry, but you'll have to be more specific about what you want.
(4) By anonymous on 2022-03-13 23:16:54 in reply to 3 [link] [source]
For the decimal-digits-only case, try something like
CHECK (colname NOT GLOB '*[^0-9]*')
(5.1) By Keith Medcalf (kmedcalf) on 2022-03-13 23:57:25 edited from 5.0 in reply to 1 [link] [source]
The proper place to restrict input is during input.
By the time your application gets to the point that it is storing data in the database, any input contraints should already have been handled by your application input routines.
If you mean that you wish to place a constraint on the database table so that invariant conditions are specified for the stored data, that is an entirely different matter and is a constraint on storage not on input.
You can defined storage constraints to ensure that the database stored content complies with certain invariant rules, but your input application should be ensuring that the input is correct before attempting to store it in the database.
Validating/sanitizing your inputs is not (and cannot be substituted) by storage constraints.
(7) By Holger J (holgerj) on 2022-03-14 15:37:55 in reply to 5.1 [link] [source]
Not at all.
The database is responsible for data integrity, therefore constraints are THE tool to enforce this.
Sanitizing data is a totally different topic, which is very well handled by SQLite (and also other database systems like PostgreSQL).
It's a common misconception that applications should do anything regarding data integrity besides user comfort. Databases are not mere data heaps. Don't degrade them to simple and dumb storage.
(8) By Ryan Smith (cuz) on 2022-03-14 17:00:46 in reply to 7 [source]
I'm not sure what to make of that statement.
You seem to say the exact same thing as Keith (in meaning) right after claiming to disagree very much.
Keith's thesis was that the ability of a database to ensure the integrity of data should not be confused with the responsibility to adjust/maintain/clean/format/test said data prior to storing it - all of which falls squarely and ONLY at the feet of the application.
You then say "Not at all!" and go on to explain that DBs offer great data integrity, but that "Sanitizing data is a different topic", well no, it's the very topic Keith touched on.
An analogy might be those metal curl-up barriers at the end of railway tracks - we place them there to ensure trains do not go past, but we NEVER use them as the actual stopping mechanism in day-to-day railway activity. Similarly we can add DB constraints to ensure non-conforming data cannot be added to a DB, but actually triggering such a constraint unintentionally is a failure/bug/deficiency of the calling application.
Now, I'm fairly sure you agree with that, so if a quick reading of the prior post seemed to say something different which prompted disagreement, then never mind, that happens, but if you do honestly disagree, mind elaborating fully?
(6) By Donal Fellows (dkfellows) on 2022-03-14 12:57:54 in reply to 1 [link] [source]
It depends a lot on what you mean by “numbers”. Do those numbers correspond to IDs in a table (because that's a foreign key constraint you're describing)? Otherwise, you're talking about a check constraint, and you have to think about what exactly you mean by them. After all, you shouldn't just think about whether a value is a number, but also what sort of number it is — in a mathematical sense, complex numbers are numbers as a quaternions, but you probably don't usually want to put them in a DB column — and what the permitted range of that sort of number really should be.
It's all part of understanding the data that you are putting in the DB, and that's both important and often not very easy.