A manual INSERT demonstrates the same behavior, actually. Using your Tc
table:
sqlite> insert into Tc values ('12');
Error: CHECK constraint failed: Tc
The thing about .import is that, instead of guessing what type each value
it reads is, they're all just bound to an insert statement as strings. When
the row is actually stored in the table, those strings are converted to
numeric types if the relevant columns have the appropriate affinity and it
can be done losslessly. Details:
https://www.sqlite.org/datatype3.html#type_affinity
For good or bad, check constraints appear to be evaluated before this
conversion.
On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden <[email protected]>
wrote:
> On Sun, 10 Mar 2019 17:04:46 -0400
> "James K. Lowden" <[email protected]> wrote:
>
> > Why does the .import command cause the CHECK constraint to fail, when
> > an ordinary INSERT does not?
>
> On Sun, 10 Mar 2019 14:12:33 -0700
> Shawn Wagner <[email protected]> wrote:
>
> > The check constraint is probably being evaluated (with t as a string)
> > before any type conversion to match the column affinity is done.
>
> Does anyone have a better answer? Isn't .import supposed to work like
> INSERT? If it doesn't, CHECK constraints for type safety are useless
> for tables that are loaded from files.
>
> --jkl
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
