I think it can all be done in a single table, with columns, A, B, C, type, Name, and other things, with indexes/constraints UNIQUE(A, B, C, Type) UNIQUE(Name, Type)
and quite possible an addition PRIMARY index, perhaps the default ROWID one, as neither of those UNIQUE indexes look to be ideal as a primary key for other tables that might want to refer to an item to use. On 3/28/19 1:59 AM, Roger Schlueter wrote: > Following Simons' comment, changing the schema to conform to SQL > expectations would involve having at least two tables. Consider your > second uniqueness criterion; Let's call those items a "Widget" so your > Widget table would be: > > WIDGETS > {A, B, C, .....} UNIQUE(A,B,C) > > Let's call your items whose Name is unique "Gadgets" so your Gadgets > table would be: > > GADGETS > {Name, A, B, C, ....} UNIQUE(Name) > > I assume there are other things: > > THINGS > {Type, Name, A, B, C, .....} No(?) uniqueness > > Knowing the Type of items to be updated, you know which table to use. > > On 3/27/2019 15:59, Thomas Kurz wrote: >>> Can I ask what it is that you're trying to do ? This smacks of >>> trying to add 1 to an existing value or something like that. >> Sure. I have a table of items. Each item has a type, a name, and >> properties A, B, C (and some more, but they're not relevant here). >> >> I want to enforce that items of a certain type and name are unique: >> UNIQUE (type, name). But there can be multiple items with the same >> name as long as they are of different types. >> >> Furthermore, items of a certain type that have identical properties >> A, B, C are also considered equal, regardless of their name: UNIQUE >> (type, A, B, C). >> >> I cannot use UNIQUE (type, name, A, B, C), as this would mean that >> there can be two items with the same A, B, C (and type, of course), >> but different name. On the other hand, there could be two items with >> the same same (and type, of course) but different A, B, C. >> >> Now when inserting an item that already exists (according to the >> uniqueness definition above), the existing item should be updated >> with the new name and A, B, C properties. >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users