Re: [sqlite] UPSERT with multiple constraints

Adding on to the other comments, I think the reason you can't do this is that each constraint could be violated by different rows. So with... create table foo (a int unique, b int unique); insert into foo values (1, 3), (2, 2), (3, 1); ...then when trying... insert into foo values (1, 2) on co

Re: [sqlite] UPSERT with multiple constraints

You are right. This is indeed a situation that I didn't have in mind. I will rethink the data design. Thank you very much for this comment and also to all others which gave me valuable ideas on how to handle my conflict. - Original Message - From: James K. Lowden To: sqlite-users@maili

Re: [sqlite] UPSERT with multiple constraints

On Wed, 27 Mar 2019 23:59:47 +0100 Thomas Kurz wrote: > 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 ... UNIQUE (type, name). ... > Furthermore, items of a certain type that have ide

Re: [sqlite] UPSERT with multiple constraints

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 wh

Re: [sqlite] UPSERT with multiple constraints

On 27 Mar 2019, at 10:59pm, Thomas Kurz wrote: > 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. Mmm. Your database design doesn't work the way SQL does. Your sepa

Re: [sqlite] UPSERT with multiple constraints

> 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

Re: [sqlite] UPSERT with multiple constraints

On 27 Mar 2019, at 2:40pm, Thomas Kurz wrote: > So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT > (col1, col3, col4, col5) DO UPDATE". Can't be done in a single instruction. The nearest you can get to this would involve using triggers: