I've created a table, where I have "Date of birth" column of date type. The problem is that I can insert anything and it's successfully done. I want that field to restrict opportunities like inserting strings and not related stuff. insertions wrongResults
I've searched for the solution, but I could only find codes for getting the current time in different formats. I also don't get how exactly modifiers work (https://www.sqlite.org/lang_datefunc.html).
Bar the rowid column or an alias of the rowid column, any type of value can be stored in an type of column. That is the type of column does not restrict/constrain the data that can be stored.
p.s. there is no DATE type rather due to SQLite's flexibility DATE actually has a type (type affinity) of NUMERIC (not that that matters that much). You might find Datatypes In SQLite Version 3 an interesting read or perhaps this How flexible/restricive are SQLite column types?.
the rowid and, therefore an alias thereof, column MUST be an integer. Although typically you allow SQLite to assign the value.
You should either check the data programatically or alternately use a CHECK constraint when defining the column in the CREATE TABLE SQL.
A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. The expression of a CHECK constraint may not contain a subquery.
SQL As Understood By SQLite - CREATE TABLE
Consider the following code :-
DROP TABLE IF EXISTS mychecktable ;
CREATE TABLE IF NOT EXISTS mychecktable (mycolumn BLOB CHECK(substr(mycolumn,3,1) = '-'));
INSERT INTO mychecktable VALUES('14-03-1900');
INSERT INTO mychecktable VALUES('1900-03-14'); -- ouch 3rd char not -
The is will result in :-
DROP TABLE IF EXISTS mychecktable > OK > Time: 0.187s CREATE TABLE IF NOT EXISTS mychecktable (mycolumn BLOB CHECK(substr(mycolumn,3,1) = '-')) > OK > Time: 0.084s INSERT INTO mychecktable VALUES('14-03-1900') > Affected rows: 1 > Time: 0.206s INSERT INTO mychecktable VALUES('1900-03-14') > CHECK constraint failed: mychecktable > Time: 0s
Usually you would enforce the correct format in your application, but you can also add constraints to your table definition to prevent this, e.g.,
CREATE TABLE users(...,
DoB TEXT CHECK(DATE(DoB) NOT NULL AND DATE(DoB)=DoB)
)
