Unique constraint violation found while creating new attribute

[romfeo]

I am trying to create a new product attribute in Magento ver. 2.3.5-p1.

When I click on Save Attribute I get an error message saying:

"Unique constraint violation found"

The only relevant record I can find in exception log says:

exception.log:[2021-04-12 10:00:36] main.CRITICAL: Report ID: webapi-60741a44ca43f; Message: SQLSTATE[23000]: Integrity constraint violation:
1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`eav_attribute_option_value`, CONSTRAINT `EAV_ATTR_OPT_VAL_OPT_ID_EAV_ATTR_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `eav_attribute_option` (`option_id`) ON DELETE CASCADE),
query was: INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES (?, ?, ?) 
{"exception":"[object] (Exception(code: 23000): Report ID: webapi-60741a44ca43f; Message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`eav_attribute_option_value`, CONSTRAINT `EAV_ATTR_OPT_VAL_OPT_ID_EAV_ATTR_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `eav_attribute_option` (`option_id`) ON DELETE CASCADE), 
query was: INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES (?, ?, ?) at /home/pen-paper/public_html/vendor/magento/framework/Webapi/ErrorProcessor.php:208, Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: 
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`eav_attribute_option_value`, CONSTRAINT `EAV_ATTR_OPT_VAL_OPT_ID_EAV_ATTR_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `eav_attribute_option` (`option_id`) 
ON DELETE CASCADE), query was: INSERT INTO `eav_attribute_option_value` (`option_id`, `store_id`, `value`) VALUES (?, ?, ?) at
/home/pen-paper/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`eav_attribute_option_value`, CONSTRAINT `EAV_ATTR_OPT_VAL_OPT_ID_EAV_ATTR_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `eav_attribute_option` (`option_id`) ON DELETE CASCADE) at /home/pen-paper/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []                         

I am not even certain that the error above is corellated to my issue

I also tried to manually insert a new attribute with the following SQL statement

INSERT INTO `eav_attribute` (`entity_type_id`,
    `attribute_code`,
    `backend_model`,
    `backend_type`,
    `backend_table`,
    `frontend_model`,
    `frontend_input`,
    `frontend_label`,
    `frontend_class`,
    `source_model`,
    `is_required`,
    `is_user_defined`,
    `default_value`,
    `is_unique`,
    `note`
  ) VALUES (
    1,
    "skrouz",
    NULL,
    "varchar",
    NULL,
    NULL,
    "text",
    "skrouz",
    NULL,
    NULL,
    0,
    0,
    NULL,
    0,
    NULL
)

The error I got in the console was:

ERROR 1062 (23000): Duplicate entry '65535' for key 'PRIMARY'

Now looking at the description of the  eav_attribute table I can see that the primary key, attribute_id, is of type smallint(5) unsigned (autoincrement).

Could it be, that due to the count of attribute values, I am exceeding the limits of small int? Can I alter the table safely to make the field an int?

[LitExtension Expert]

Hi @romfeo,

You are using the wrong data type please convert it to "int"

Hope this can help you! Let me know if you need further assistance.

________

If issue solved, Click Kudos & Accept as Solution.

[kulesovm59159b]

The reason for this error can be the creation of duplicate values in the database table.

MyGroundBiz