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
