On Wed, Apr 20, 2016 at 8:09 PM, Dan Kennedy <danielk1977 at gmail.com> wrote:
> ?If I read that correctly then a field has to be NULL or point to a record >> in the other table. I want it to point to a record if it is an INT and >> whem >> it is TEXT it is just the text. (And it should only be allowed to be an >> INT >> or TEXT.) >> > > I don't think you can use a constraint in SQLite for that. You could write > a trigger to check that only valid values are inserted into the table > though (and the corresponding trigger for updates, and for updates and > deletes on the referenced table). You could, if SQLite supported virtual/computed columns, and FKs on such vcolumns. I use this very technique in Oracle (for enforced polymorphic FKs). Basically something like (syntax not checked): create table parent ( id integer primary key not null, ... ); create table child ( int_or_text_col, parent_vcol int as (case when typeof(int_or_text_col)='number' then int_or_text_col else null end) references parent(id) ); SQlite already supports expression indexes, and that's basically the same thing. FWIW. --DD
