CODEWITHSUNDEEP
mysqlsql
Suhayb
Suhayb

Reputation: 3271

Strange update statement behavior

Executing the following query 

UPDATE tbl_outcome SET  `type` = 'do', `statement` = 'NAREs, private sector organizations and NGOs develop innovation platforms, disseminate  market information to processors and develop their marketing skills', 
`actionsite_id` = 440, `code` = 'G&T-I' WHERE (outcome_id = '546');

will cause this error

SQLSTATE[23000]: Integrity constraint violation:
 1062 Duplicate entry 'NAREs, private sector organizations and NGOs develop innovation ' for key 'statement'

noting that there is another row shares the same statement value but why it rejects the duplication 

CREATE TABLE `tbl_outcome` (
      `outcome_id` int(11) NOT NULL AUTO_INCREMENT,
      `type` varchar(2) NOT NULL,
      `statement` varchar(255) DEFAULT NULL,
      `actionsite_id` int(11) NOT NULL,
      `code` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`outcome_id`),
      UNIQUE KEY `statement` (`statement`,`actionsite_id`),
      KEY `actionsite_id` (`actionsite_id`),
      CONSTRAINT `tbl_outcome_ibfk_1` FOREIGN KEY (`actionsite_id`) REFERENCES `tbl_actionsite` (`actionsite_id`) ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8;

mysql> mysql> SELECT statement FROM crpcoreix.tbl_outcome limit 5;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| statement                                                                                                                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   Female and male farmers/shakeholders use/adopt/implement methods/tools/approaches/technologies/NRMpractices/Varieties/framework/concepts                                                                                            |
|   Female and male farmers/stakeholders (inc.youth) awareness, skills and knowledge increased                                                                                                                                          |
|   Female and male farmers/stakeholders (inc.youth) awareness, skills and knowledge increased                                                                                                                                          |
|   Interventions better targeted/prioritized by stakeholders                                                                                                                                                                           |
|  At least (3-15?) potato candidate varieties (short season, heat tolerant, and pest/disease resistant) compatible  with cereal crops: identified and recommended for release in at least  three countries (China, Bangladesh, India) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Upvotes: 0

Views: 39

Answers (2)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

Because there is a unique Key constraint on the Statement column named :

UNIQUE KEY `statement` (`statement`,`actionsite_id`),

Hope this helps!!!

Upvotes: 1

kapilpatwa93
kapilpatwa93

Reputation: 4411

Try this

 SHOW INDEX FROM tbl_outcome

This will give you index_name for the Unique key

then run following query

ALTER TABLE tbl_outcome DROP INDEX index_name

This will delete Unique key constraint

Upvotes: 0

Related Questions