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
