mysql constraint or trigger

Question

How to make sure that a couple of rows (A,B) values can be duplicated but can have only one corresponding value in row C in the same table ?

Suppose i am inserting these rows in this order this the output i want to have.

EDIT: what i want is that when A,B,C is inserted for the first time value in C will be the unique possible value for the couple (A,B)

this means, if i, insert (0,0) with C = 1 when there is no other (0,0) couples in (A,B) then the row is accepted.But if i add (0,0) for the second(third ..) time; then C has to be equal to 1.

example :

A | B | C | D | F  ...
_________________
0 | 0 |1
0 | 0 |1
0 | 0 |2 --> not allowed
1 | 0 |3 --> allowed
1 | 0 |2 --> not allowed
1 | 0 |3 --> allowed

more examples please. and what about (allowed/not) 0 | 0 |1 ? — M0rtiis, Sep 01 '15 at 12:43
Your rules on what is allowed and not allowed are not clear. — Gordon Linoff, Sep 01 '15 at 12:45
Check the [URL](http://stackoverflow.com/questions/32090658/duplicate-data-stop-in-mysql) solved using Trigger search the text on the page "DB Trigger" — Satender K, Sep 01 '15 at 13:31

M0rtiis · Answer 1 · 2015-09-01T14:39:09.560

try this trigger

DELIMITER $$
CREATE TRIGGER wtf_dupes BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    SET @ok := TRUE;

    SELECT FALSE INTO @ok FROM your_table
    WHERE 
            A = new.A 
        AND B = new.B 
        AND C <> new.C
    LIMIT 1 
    ;

    IF NOT @ok THEN
        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = 'YourMessageAboutDupeError';
    END IF;
END $$
DELIMITER ;

about how to trap signals you should do your own investigation: http://dev.mysql.com/doc/refman/5.5/en/signal.html#signal-effects

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Although there is a way to create a trigger that checks another table or view for already existing value combinations, there is a question on canceling insert from within a trigger in MySQL and the accepted answer says that there is no such feature. So I think you have to implement this in application logic or move the insert to a procedure.

edited May 23 '17 at 12:23

Community

1
1

answered Sep 01 '15 at 14:30

Laszlo T

1,165
10
22

mysql constraint or trigger

2 Answers2