8

I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.

CREATE TABLE student
    (Id int primary key, Name varchar(50), Symbol varchar(50),
      CONSTRAINT col1_unique UNIQUE (Name),
      CONSTRAINT col2_unique UNIQUE (Symbol)
    ); 

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (1, 'John', 'J'),
    (2, 'David', 'D'),
    (3, 'Will', 'W');

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (4, 'Jeremy', 'J')
   on conflict(Name) DO UPDATE
   set Name = 'Jeremy';

Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?

Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce

  • you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data. – Akina Oct 3 '18 at 8:34
  • @Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes. – appl3r Oct 3 '18 at 8:40
  • One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used. – Akina Oct 3 '18 at 8:42
  • While that's true that's quitre a bother : see the sample in the answer of Michael Green. Splitting such a simple table into 4 tables is definitively a headache, thought since it's a simple dictionnary, it's not like you should need the "ON CONFLICT" option. – Walfrat Oct 3 '18 at 13:07
8

There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.

I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.

  • Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using the MERGE statement). The pg ON CONFLICT DO UPDATE was only added in 9.5 and is much more limited. – appl3r Oct 3 '18 at 11:49
  • 1
    @appl3r it's only an implementation detail. You can do ON CONFLICT DO NOTHING without mentioning unique constraints and it will consider all of them (and do nothing). When you want to do ON CONFLICT DO UPDATE, you have to declare a column or a constraint and only one. The restriction might be lifted in future versions. – ypercubeᵀᴹ Oct 3 '18 at 13:41
  • I'm a bit confused by your first paragraph. Let's say we split the OP's Student(Id, Name, Symbol) table into S1(Id, Name) and S2(Id, Symbol) to try and make it into DK/NF. What would be the (domain and) key constraints on S2, and how would they ensure that each student has both a single unique Id and a single unique Symbol? – Ilmari Karonen Oct 3 '18 at 13:47
  • 1
    The question was (kind of) "If multiple UNIQUE indexes are fine, why does PostgreSQL require that an arbiter index is specified for ON CONFLICT ... DO UPDATE and unfortunately this answer does not address that point. – AndreKR Oct 3 '18 at 13:59
  • @AndreKR it addresses the (bolded): "was (this Postgres feature) designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index?" You can certainly add another answer, addressing the point you mention – ypercubeᵀᴹ Oct 3 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.