CODEWITHSUNDEEP
mysqlduplicatesunique
Tobias Baumeister
Tobias Baumeister

Reputation: 2147

Mysql Unique behaviour with duplicated entries

If I set two columns to be unique and then insert the same values twice, how will the database behave? Will it ignore the second insert or replace the first one?

Upvotes: 0

Views: 78

Answers (2)

Dave C
Dave C

Reputation: 37

'columns' are fields and each field is separately unique.

CREATE TABLE `mydb`.`example` (
  `rid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `basecolor` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `trimcolor` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  UNIQUE (
     `basecolor`
  )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `example` ADD UNIQUE (
  `trimcolor`
)

INSERT INTO example (basecolor, trimcolor) VALUES ('red', 'red'), ('green', 'green'), ('blue', 'green')

#1062 - Duplicate entry 'green' for key 'trimcolor'

SELECT * FROM example  

+------------------------------+  
| rid  | basecolor | trimcolor |  
|======|===========|===========|  
| 1    | red       | red       |  
| 2    | green     | green     |  
+------------------------------+

Upvotes: 0

woofmeow
woofmeow

Reputation: 2408

From the manual

Normally, errors occurs for data-change statements (such as INSERT or UPDATE) that would violate primary-key, unique-key, or foreign-key constraints.

However if you use the IGNORE keyword then it will be allowed.

Read more here

Upvotes: 2

Related Questions