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