I am creating a table in RSQLite and would like to avoid errors that may come from misspecification of data types. I tried to add a check(typeof()) constraint. However, SQLite appears to be doing an automatic conversion from numeric to text despite the check constraint. I also tried to create a "STRICT" table but the problem persists. Can anyone help me to figure out what's going on and avoid the automatic conversion? Many thanks!
The code below adds a check constraint to the ID variable specified as a type "text" in the "TEST" table. However, when a numeric value is inserted, instead of a text value, no error is flagged. When querying the data, it appears that the numeric value has been automatically converted into a character value.
dbExecute(conn = dbconn, "CREATE TABLE TEST(
- ID TEXT CHECK(TYPEOF(ID) = 'text'))") [1] 0
data <- data.frame(ID = 1) str(data) 'data.frame': 1 obs. of 1 variable: $ ID: num 1 dbWriteTable(conn = dbconn, "TEST", data, append = T)
out <- dbSendQuery(conn = dbconn, statement = "select * from TEST") fetch(out) ID 1 1.0 str(fetch(out)) 'data.frame': 0 obs. of 1 variable: $ ID: chr