CODEWITHSUNDEEP
mysql
Will59
Will59

Reputation: 1883

On mySql INSERT, how to throw an error only when existing duplicate differs

I have a table in my database with two columns, A and B.

  • A is the primary key, unique
  • B cannot be null

When I insert (newA, newB), I want to:

  • insert if newA doesn't exist
  • ignore if (newA, newB) already exists (no errors, no effect or overwrite same/existing pair)
  • return an error if a different couple exists with A = newA

This looks really simple, but I don't really see how to do that! I guess one way would be to set the primary key as (A,B), would that do it?

Upvotes: 2

Views: 1678

Answers (3)

Will59
Will59

Reputation: 1883

Looking back at my old pending questions, the answer here is quite simple:

  • make (A,B) the key
  • create an index on A to make it unique

This will enforce the desired rules.

Upvotes: 0

fifonik
fifonik

Reputation: 1606

You can try this:

  1. Create unique key (A, B). This will not allow the record with the same (A, B) combination to be inserted

  2. Use INSERT IGNORE instead of INSERT so unique constrain you created on step 1 will not cause an error but will be silently ignored.

  3. Manually check existence record with the same A value in before insert/update triggers and throw an error if such record already there. Something like this:

CREATE TRIGGER `tbl_before_insert` BEFORE INSERT ON `tbl` FOR EACH ROW BEGIN
    IF EXISTS(SELECT * FROM `tbl` WHERE `tbl`.`A` = NEW.`A` AND `tbl`.`B` != NEW.`B`) THEN
        SET @msg = CONCAT('Record with A=', NEW.A, ' already exists');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;
END

Upvotes: 1

Tharindu
Tharindu

Reputation: 339

You can keep A as the Primary Key while making the B field UNIQUE.

  • Existing A Insert => It will throw an error (as it is the primary key).
  • If new A, new B => It won't be a problem.
  • If new A, old B => Because we made the B column unique, It'll throw an error.

Upvotes: 0

Related Questions