MySQL multiple checks and insert if atleast one is not duplicate

Question

I have a table where none of the columns are a key (Because all can be duplicate)

I want to insert var, var2, var3, var4, var5 into columns id, id2, id3, id4, id5 where there is atleast one non-duplicate present.

i.e if all are duplicate except id3, make a new entry but if all are duplicate don't insert.

Do you mean that every combination of (id, id2, id3, id4, id5) together has to be unique? Would a [multi-column UNIQUE constraint](https://stackoverflow.com/questions/635937/) do what you want? — Wander Nauta, Jun 30 '22 at 12:50
what i mean is, if in id, id2, id3, id4, id5. atleast one is not duplicate make a new entry. but if all of them is duplicate don't make an identical entry. not sure if that is what you mentioned. if so yes. — Sombre Dreamer, Jun 30 '22 at 12:50
If the columns are all defined as NOT NULL, then the multi-column unique constraint mentioned by @WanderNauta would do that. If any of the columns allows NULLs, it probably won't work the way you want it to. — Bill Karwin, Jun 30 '22 at 12:53
I've one column that allows NULLs. so is there no way I can accomplish this? — Sombre Dreamer, Jun 30 '22 at 12:55

score 0 · Answer 1 · answered Jun 30 '22 at 13:15

No problems, create unique composite index. For nullable column use COALESCE and replace NULL with some char/string which cannot present in the value. For a delimiter use the char/string which cannot be a prefix/postfix for any value.

CREATE TABLE test (
    f1 INT NOT NULL,
    f2 INT NOT NULL,
    f3 INT NOT NULL,
    f4 INT NOT NULL,
    f5 INT NULL,       -- nullable column
    UNIQUE ( (CONCAT_WS(CHAR(0), f1,f2,f3,f4,COALESCE(f5, CHAR(0)))) )
);

DEMO. CHAR(0) is used for both delimiter and NULL replacement.

MySQL multiple checks and insert if atleast one is not duplicate

1 Answers1