How to avoid this kind of duplicate?

Question

This is my table for many to many relationship:

Related:
-id
-id_postA
-id_postB

I want this:

If for example there is a row with id_postA = 32 and id_postB = 67 then it must ignore the insertion of a row with id_postA = 67 AND id_postB = 32.

score 1 · Answer 1 · edited May 23 '17 at 11:49

One option would be to create a unique index on both columns:

CREATE UNIQUE INDEX uk_related ON related (id_postA, id_postB);

And then prevent "duplicates by order inversion" using a trigger, ordering id_postA and id_postB on INSERT and UPDATE:

CREATE TRIGGER order_uk_related
BEFORE INSERT     -- Duplicate this trigger also for UPDATE
ON related        -- As MySQL doesn't support INSERT OR UPDATE triggers
FOR EACH ROW
BEGIN
    DECLARE low INT;
    DECLARE high INT;

    SET low = LEAST(NEW.id_postA, NEW.id_postB);
    SET high = GREATEST(NEW.id_postA, NEW.id_postB);

    SET NEW.id_postA = low;
    SET NEW.id_postB = high;
END;

As you can see in this SQLFiddle, the fourth insert will fail, as (2, 1) has already been switched to (1, 2) by the trigger:

INSERT INTO relation VALUES (1, null, null)
INSERT INTO relation VALUES (2, null, null)
INSERT INTO relation VALUES (3, 2, 1)
INSERT INTO relation VALUES (4, 1, 2)

Function-based indexes

In some other databases, you might be able to use a function-based index. Unfortunately, this is not possible in MySQL (Is it possible to have function-based index in MySQL?). If this were an Oracle question, you'd write:

CREATE UNIQUE INDEX uk_related ON related (
    LEAST(id_postA, id_postB), 
    GREATEST(id_postA, id_postB)
);

@xRobot: Of course, you can use a [`UNIQUE` constraint](http://dev.mysql.com/doc/refman/5.7/en/create-table.html) in a table definition — Lukas Eder, May 10 '13 at 17:07
but "UNIQUE KEY `id_posts` (`id_postA`,`id_postB`)" also considers the values reversed? — xRobot, May 10 '13 at 17:11
No. That's why you have to guarantee that they're in order, e.g. by using a trigger. — Lukas Eder, May 10 '13 at 17:12

score 0 · Answer 2 · answered May 10 '13 at 16:58

0

you can include a where like:

For example

insert into table_name
(id_postA 
,id_postB
select 
col1,
col2
from table_1
where where (cast(col1 as varchar)+'~'+cast(col2 as varchar))
    not in (select cast(id_postB as varchar)+'~'+cast(id_postA as varchar) from table_name)

answered May 10 '13 at 16:58

Prahalad Gaggar

11,389
16
53
71

is there not a way to do this in the table definition ? – xRobot May 10 '13 at 17:00
Don't use string concatenation when you could use row value expression comparison: `where (col1, col2) not in (select id_postB, id_postA from table)` – Lukas Eder May 10 '13 at 17:33
@LukasEder I would like to know its ill-effects – Prahalad Gaggar May 11 '13 at 05:33
I don't know MySQL well enough to tell you about potential "ill-effects", but using row value expressions has a big chance of being faster than using string concatenation, as indexes may be leveraged, and no additional memory / CPU has to be used for the concatenation... I'm sure @BillKarwin would know more, here... – Lukas Eder May 11 '13 at 07:10

score 0 · Answer 3 · answered May 10 '13 at 17:25

0

If you always insert these with A < B, you won't have to worry about the reverse being inserted. This can be done with a simple sort, or a quick comparison before inserting.

Join tables like this are by their very nature uni-directional. There is no automatic method for detecting the reverse join and blocking it with a simple UNIQUE index.

Normally what you'd do, though, is insert in pairs:

INSERT INTO related (id_postA, id_postB) VALUES (3,4),(4,3);

If this insert fails, then one or both of those links is already present.

answered May 10 '13 at 17:25

tadman

208,517
23
234
262

To truly enforce `A < B` it might not be a bad idea to write a [trigger](http://stackoverflow.com/a/16487362/521799) – Lukas Eder May 10 '13 at 17:29
Honestly, whenever I hear "trigger", I hear "bad idea". – tadman May 10 '13 at 18:38
Well, short of function-based unqiue indexes, it is hard to enforce `A < B` ordering without a trigger in the database. Enforcing it in the application seems OK at first, but developers tend to forget... – Lukas Eder May 11 '13 at 07:14
Developers only "forget" if there's no method that performs this operation reliably, and there's no unit tests to "remind" them of this. You cannot make the database responsible for catching implementation errors. Once you start down the trigger road, half your application becomes stored procedures and trigger code that are significantly more difficult to manage and test. – tadman May 11 '13 at 17:45

How to avoid this kind of duplicate?

3 Answers3

Function-based indexes