On 2016/04/20 6:21 PM, Cecil Westerhof wrote: > 2016-04-20 18:07 GMT+02:00 R Smith <rsmith at rsweb.co.za>: > >> >> On 2016/04/20 6:04 PM, Cecil Westerhof wrote: >> >>> I am thinking about creating a table where a certain field will be a >>> description, or a key to the description. Would it be possible to have a >>> constraint on this field that it is an id, that this id points to an >>> existing record in a description table? >>> >>> >> https://www.sqlite.org/foreignkeys.html > > ?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.)
That is a query function, not a Schema/field function. For instance, you could do this: Select A.ID, A.Name, COALESCE(B.Description, A.ForeignID) FROM myTable1 AS A LEFT JOIN myForeignTable AS B ON B.ID = A.ForeignID This way, if there exists a valid entry in the "myForeignTable" with ID = ForeignID in the primary table, then it displays that item's B.Description field. If not, then it displays the primary table's A.ForeignID field which may then be a description or whatever else you like it to be. You can also achieve this by adding user-defined functions via the API, or simply creating Virtual tables that do the same, but simply making the query do the work is best. Perhaps even a view like this: CREATE VIEW fV1 AS SELECT A.ForeignID, COALESCE(B.Description, A.ForeignID) AS Description FROM myTable1 AS A LEFT JOIN myForeignTable AS B ON B.ID = A.ForeignID ); Now whenever you do a query you can simply join in the view on ForeignID and refer its Description field which would contain the good value. While this all will work nicely.... As a matter of clarity, form and efficiency, I'd go with Simon's suggestion: use 2 columns - you will thank yourself later. Cheers, Ryan
