0
id | source | target | desc
--------------------------------------------
0  | hello  | hola   | this is a description   //Valid insert
1  | hello  | no     | this is a description   //Valid insert
2  | what   | what   | desc                    //Valid insert
3  | hello  | hola   | testing                 //INVALID insert

I want the row with ID 3 to error when inserted because it's source matches another row's source AND its target matches that same other row's target.

I know that I could do this in PHP, but I would prefer it done within the MySQL RDBMS.

3
  • add unique constraint on columns source and target
    – user168186
    Mar 10, 2020 at 19:53
  • Wouldn't that prevent me from having two rows with the same source? Such as row 0 and 1?
    – Wyatt
    Mar 10, 2020 at 20:04
  • @Wyatt: Declaring them unique individually would. Declaring a set of columns unique, however, means you want unique combinations of those columns' values.
    – Andriy M
    Mar 13, 2020 at 7:18

1 Answer 1

2
CREATE TABLE `t1` (
  `id` int(11) ,
  `source` varchar(30) ,
  `target` varchar(30) ,
  `desc` varchar(100) ,
  PRIMARY KEY `pk_id` (`id`),
  UNIQUE KEY `uc_source_target` (`source`,`target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

or on existing table

 ALTER TABLE t1 ADD CONSTRAINT uc_source_target UNIQUE (`source`,`target`);


INSERT INTO t1
    (`id`, `source`, `target`, `desc`)
VALUES
    (0, 'hello', 'hola', 'this is a description'),
    (1, 'hello', 'no', 'this is a description'),
    (2, 'what', 'what', 'desc');




 mysql>  INSERT INTO t1
    ->            (`id`,`source`,`target`,`desc`)
    ->     VALUES
    ->             (3,'hello','hola','testing');
ERROR 1062 (23000): Duplicate entry 'hello-hola' for key 'uc_source_target'
2
  • Surely, you mean ALTER TABLE t1 ADD CONSTRAINT uq_source_target UNIQUE (source, target); - note the use of the UNIQUE keyword! Typo! :-)
    – Vérace
    Mar 15, 2020 at 22:56
  • I forgot that thanks for pointing that out
    – user168186
    Mar 16, 2020 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.