2016-04-20 19:27 GMT+02:00 R Smith <rsmith at rsweb.co.za>: > > > 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. >
?I think you and Simon are right. I only need to make a check that exactly one of the two is NULL. (There should be a description, but not more as one.) But that is not to hard. -- Cecil Westerhof
