mysql - 更改表添加有错误的列

Question

我正在表中添加新列，但在进行更改时收到错误。

请看下面:

ALTER TABLE dbname.table_name ADD COLUMN column_name TINYINT(1) UNSIGNED DEFAULT 0;
错误 1062 (23000):键“PRIMARY”的重复条目“300866”

mysql> show create table dbname.table_name\G
*************************** 1. row ***************************
       Table: table_name
Create Table: CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) NOT NULL,
  `status` enum('active','inactive','deleted') NOT NULL DEFAULT 'active',
  `status_supplier_config` enum('active','inactive','deleted') NOT NULL DEFAULT 'active',
  `name` varchar(255) NOT NULL,
  `description` text COMMENT 'Description of the product',
  `fk_catalog_brand` int(10) unsigned DEFAULT NULL,
  `primary_category` int(10) unsigned DEFAULT NULL,
  `fk_catalog_import` int(10) unsigned DEFAULT NULL,
  `display_if_out_of_stock` tinyint(1) DEFAULT '0',
  `fk_catalog_config_group` int(10) unsigned DEFAULT NULL,
  `fk_catalog_attribute_set` int(10) unsigned NOT NULL,
  `pet_status` set('creation','edited','images') DEFAULT NULL,
  `pet_approved` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `created_by_config` varchar(50) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by_config` varchar(50) DEFAULT NULL,
  `activated_at` datetime DEFAULT NULL,
  `supplier_name` varchar(255) DEFAULT NULL,
  `sku_supplier_config` varchar(255) DEFAULT NULL COMMENT 'SKU of product at supplier',
  `creation_source_config` varchar(255) NOT NULL DEFAULT '',
  `product_line` varchar(255) DEFAULT NULL COMMENT 'Product Line/Range/Sub-Brand',
  `model` varchar(255) DEFAULT NULL COMMENT 'Model ID of Manufacturer part number',
  `fk_catalog_attribute_option_global_supplier_type` int(10) unsigned DEFAULT NULL COMMENT 'Type of supplier',
  `fk_catalog_attribute_option_global_season` int(10) unsigned DEFAULT NULL COMMENT 'Season',
  `fk_catalog_attribute_option_global_gender` int(10) unsigned DEFAULT NULL COMMENT 'Gender',
  `fk_catalog_attribute_option_global_production_country` int(10) unsigned DEFAULT NULL COMMENT 'Production country of product',
  `imported` tinyint(1) unsigned DEFAULT NULL COMMENT 'Is the product imported from outside Australia?',
  `note` varchar(255) DEFAULT NULL COMMENT 'Note / comment',
  `source_url_1` text COMMENT 'Product URL (scraped products)',
  `source_cost_1` decimal(11,2) DEFAULT NULL COMMENT 'Cost Supplier 1 (Gross)',
  `source_ship_cost_1` decimal(11,2) DEFAULT NULL COMMENT 'Ship Cost Supplier 1 (Gross)',
  `source_ship_time_1` int(255) DEFAULT NULL COMMENT 'Averate Lead Shipping Time by Supplier',
  `fk_catalog_attribute_option_global_source_stock_1` int(10) unsigned DEFAULT NULL COMMENT 'Stock Supplier 1',
  `source_url_2` text COMMENT 'Product URL (scraped products)',
  `source_cost_2` decimal(11,2) DEFAULT NULL COMMENT 'Cost Supplier 2 (Gross)',
  `source_ship_cost_2` decimal(11,2) DEFAULT NULL COMMENT 'Ship Cost Supplier 2 (Gross)',
  `source_ship_time_2` int(255) DEFAULT NULL COMMENT 'Averate Lead Shipping Time by Supplier',
  `fk_catalog_attribute_option_global_source_stock_2` int(10) unsigned DEFAULT NULL COMMENT 'Stock Supplier 2',
  `source_url_3` text COMMENT 'Product URL (scraped products)',
  `source_cost_3` decimal(11,2) DEFAULT NULL COMMENT 'Cost Supplier 3 (Gross)',
  `source_ship_cost_3` decimal(11,2) DEFAULT NULL COMMENT 'Ship Cost Supplier 3 (Gross)',
  `source_ship_time_3` int(255) DEFAULT NULL COMMENT 'Averate Lead Shipping Time by Supplier',
  `fk_catalog_attribute_option_global_source_stock_3` int(10) unsigned DEFAULT NULL COMMENT 'Stock Supplier 3',
  `source_image_url` text COMMENT 'Image URL (scraped products)',
  `category_string` varchar(255) DEFAULT NULL COMMENT 'Category Destination',
  `product_measures` varchar(255) DEFAULT NULL,
  `product_weight` varchar(255) DEFAULT NULL,
  `package_height` decimal(6,2) DEFAULT NULL,
  `package_length` decimal(6,2) DEFAULT NULL,
  `package_width` decimal(6,2) DEFAULT NULL,
  `package_weight` decimal(10,2) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL COMMENT 'Main color of product',
  `main_material` varchar(255) DEFAULT NULL COMMENT 'Material of the product',
  `product_type` varchar(255) DEFAULT NULL COMMENT 'Type',
  `designer` varchar(255) DEFAULT NULL COMMENT 'Designer',
  `complementary_products` varchar(1000) DEFAULT NULL COMMENT 'SKUs of products which might be perfect additions',
  `short_description` text COMMENT 'Key features as bullet points',
  `package_content` text,
  `manufacturer_txt` text COMMENT 'Manufacturer Text',
  `care_label` text COMMENT 'Special care instructions',
  `product_warranty` text COMMENT 'Product warranty',
  `product_warranty_en` text,
  `auk_salesrank` int(10) DEFAULT NULL,
  `capacity_ml` int(10) DEFAULT NULL,
  `source_contract_no_one` varchar(255) DEFAULT NULL,
  `source_contract_no_two` varchar(255) DEFAULT NULL,
  `source_contract_no_three` varchar(255) DEFAULT NULL,
  `fk_catalog_attribute_option_global_new_to_market` int(10) unsigned DEFAULT NULL,
  `installment_eligible` tinyint(1) unsigned DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text,
  `rich_tag_snippet` text,
  `product_owner` int(10) unsigned DEFAULT NULL,
  `caching_hash` varchar(32) DEFAULT NULL,
  `complementary_product_list` text,
  `substitute_product_list` text,
  `product_class` varchar(20) DEFAULT NULL,
  `noindex` tinyint(1) unsigned DEFAULT NULL,
  `required_serial` tinyint(1) unsigned DEFAULT '0',
  `is_returnable` tinyint(1) unsigned DEFAULT '1',
  `keywords` text,
  `product_invisible` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `warranty_list` varchar(128) DEFAULT NULL,
  `manufacturing_bar_code_required` tinyint(1) unsigned DEFAULT NULL,
  `genuine_product` tinyint(1) unsigned DEFAULT NULL,
  `cod_not_available` tinyint(1) unsigned DEFAULT NULL,
  `promotion_texts` varchar(255) DEFAULT NULL,
  `marketplace_parent_sku` varchar(255) DEFAULT NULL,
  `marketplace_children_skus` text,
  `marketplace_manual_overridden_list` text,
  `sku_payment_blacklist` text,
  `original_name` varchar(255) DEFAULT NULL,
  `video` varchar(255) DEFAULT NULL,
  `disable_automated_seo_text` tinyint(1) unsigned DEFAULT '0',
  `package_dimension_mandatory` tinyint(1) unsigned DEFAULT NULL,
  `fk_catalog_attribute_option_global_product_visibility` int(10) unsigned DEFAULT NULL,
  `disable_notify_me` tinyint(1) DEFAULT '0',
  `force_use_sold_fulfilled_by` tinyint(1) unsigned DEFAULT '0',
  `display_out_of_stock_even_still_available` tinyint(1) unsigned DEFAULT NULL,
  `disable_rich_relevance` tinyint(1) unsigned DEFAULT '0',
  `published_time` datetime DEFAULT NULL,
  `flash_sale_status` varchar(255) DEFAULT '0' COMMENT 'The flashsale status',
  `flash_sale_registration_start_date` datetime DEFAULT NULL COMMENT 'The date when registration for flash sales starts',
  `flash_sale_registration_end_date` datetime DEFAULT NULL COMMENT 'The date when registration for flash sales ends',
  `flash_sale_start_date` datetime DEFAULT NULL COMMENT 'The date when flash sales starts',
  `flash_sale_end_date` datetime DEFAULT NULL COMMENT 'The date when flash sales ends',
  `flash_sale_url` varchar(255) DEFAULT NULL COMMENT 'The flashsale url',
  `shipping_section_details_txt` text,
  `buyer_protection_details_txt` text,
  `name_en` varchar(255) DEFAULT NULL,
  `description_en` text,
  `fk_catalog_attribute_option_global_return_policies` int(10) unsigned DEFAULT '2',
  `added_group_time` datetime DEFAULT NULL,
  `fk_catalog_attribute_option_global_bullet_point_options` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `fk_catalog_attribute_set` (`fk_catalog_attribute_set`),
  KEY `fk_catalog_brand` (`fk_catalog_brand`),
  KEY `fk_catalog_config_group` (`fk_catalog_config_group`),
  KEY `fk_catalog_import` (`fk_catalog_import`),
  KEY `pet_approved` (`pet_approved`),
  KEY `name` (`name`),
  KEY `created_at` (`created_at`),
  KEY `updated_at` (`updated_at`),
  KEY `activated_at` (`activated_at`),
  KEY `fk_catalog_attribute_option_global_season` (`fk_catalog_attribute_option_global_season`),
  KEY `fk_catalog_attribute_option_global_gender` (`fk_catalog_attribute_option_global_gender`),
  KEY `fk_catalog_attribute_option_global_source_stock_1` (`fk_catalog_attribute_option_global_source_stock_1`),
  KEY `fk_catalog_attribute_option_global_source_stock_2` (`fk_catalog_attribute_option_global_source_stock_2`),
  KEY `fk_catalog_attribute_option_global_source_stock_3` (`fk_catalog_attribute_option_global_source_stock_3`),
  KEY `fk_catalog_attribute_option_global_supplier_type` (`fk_catalog_attribute_option_global_supplier_type`),
  KEY `fk_catalog_attribute_option_global_new_to_market` (`fk_catalog_attribute_option_global_new_to_market`),
  KEY `status` (`status`),
  KEY `fk_catalog_attribute_option_global_production_country` (`fk_catalog_attribute_option_global_production_country`),
  KEY `primary_category_to_catalog_category` (`primary_category`),
  KEY `catalog_config_supplier_fk` (`product_owner`),
  KEY `marketplace_parent_sku` (`marketplace_parent_sku`),
  KEY `fk_catalog_attribute_option_global_product_visibility` (`fk_catalog_attribute_option_global_product_visibility`),
  KEY `fk_catalog_attribute_option_global_return_policies` (`fk_catalog_attribute_option_global_return_policies`),
  KEY `fk_catalog_attribute_option_global_bullet_point_options` (`fk_catalog_attribute_option_global_bullet_point_options`),
  CONSTRAINT `catalog_config_ibfk_1` FOREIGN KEY (`fk_catalog_attribute_set`) REFERENCES `catalog_attribute_set` (`id_catalog_attribute_set`),
  CONSTRAINT `catalog_config_ibfk_2` FOREIGN KEY (`fk_catalog_brand`) REFERENCES `catalog_brand` (`id_catalog_brand`),
  CONSTRAINT `catalog_config_ibfk_4` FOREIGN KEY (`fk_catalog_config_group`) REFERENCES `catalog_config_group` (`id_catalog_config_group`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `catalog_config_ibfk_5` FOREIGN KEY (`fk_catalog_import`) REFERENCES `catalog_import` (`id_catalog_import`),
  CONSTRAINT `catalog_config_supplier_fk` FOREIGN KEY (`product_owner`) REFERENCES `supplier` (`id_supplier`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_catalog_attribute_option_global_bullet_point_options` FOREIGN KEY (`fk_catalog_attribute_option_global_bullet_point_options`) REFERENCES `catalog_attribute_option_global_bullet_point_options` (`id_catalog_attribute_option_global_bullet_point_options`),
  CONSTRAINT `fk_catalog_attribute_option_global_gender` FOREIGN KEY (`fk_catalog_attribute_option_global_gender`) REFERENCES `catalog_attribute_option_global_gender` (`id_catalog_attribute_option_global_gender`),
  CONSTRAINT `fk_catalog_attribute_option_global_new_to_market` FOREIGN KEY (`fk_catalog_attribute_option_global_new_to_market`) REFERENCES `catalog_attribute_option_global_new_to_market` (`id_catalog_attribute_option_global_new_to_market`),
  CONSTRAINT `fk_catalog_attribute_option_global_product_visibility` FOREIGN KEY (`fk_catalog_attribute_option_global_product_visibility`) REFERENCES `catalog_attribute_option_global_product_visibility` (`id_catalog_attribute_option_global_product_visibility`),
  CONSTRAINT `fk_catalog_attribute_option_global_production_country` FOREIGN KEY (`fk_catalog_attribute_option_global_production_country`) REFERENCES `catalog_attribute_option_global_production_country` (`id_catalog_attribute_option_global_production_country`),
  CONSTRAINT `fk_catalog_attribute_option_global_return_policies_constraint` FOREIGN KEY (`fk_catalog_attribute_option_global_return_policies`) REFERENCES `catalog_attribute_option_global_return_policies` (`id_catalog_attribute_option_global_return_policies`),
  CONSTRAINT `fk_catalog_attribute_option_global_season` FOREIGN KEY (`fk_catalog_attribute_option_global_season`) REFERENCES `catalog_attribute_option_global_season` (`id_catalog_attribute_option_global_season`),
  CONSTRAINT `fk_catalog_attribute_option_global_source_stock_1` FOREIGN KEY (`fk_catalog_attribute_option_global_source_stock_1`) REFERENCES `catalog_attribute_option_global_source_stock_1` (`id_catalog_attribute_option_global_source_stock_1`),
  CONSTRAINT `fk_catalog_attribute_option_global_source_stock_2` FOREIGN KEY (`fk_catalog_attribute_option_global_source_stock_2`) REFERENCES `catalog_attribute_option_global_source_stock_2` (`id_catalog_attribute_option_global_source_stock_2`),
  CONSTRAINT `fk_catalog_attribute_option_global_source_stock_3` FOREIGN KEY (`fk_catalog_attribute_option_global_source_stock_3`) REFERENCES `catalog_attribute_option_global_source_stock_3` (`id_catalog_attribute_option_global_source_stock_3`),
  CONSTRAINT `fk_catalog_attribute_option_global_supplier_type` FOREIGN KEY (`fk_catalog_attribute_option_global_supplier_type`) REFERENCES `catalog_attribute_option_global_supplier_type` (`id_catalog_attribute_option_global_supplier_type`),
  CONSTRAINT `primary_category_to_catalog_category` FOREIGN KEY (`primary_category`) REFERENCES `catalog_category` (`id_catalog_category`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=987565 DEFAULT CHARSET=utf8

Answer 1

试试这个:

创建表 new_table 像 table_name;
alter table new_table add new_column tinyint(1) unsigned default 1;
插入 new_table select * from table_name;

如果有效，只需删除 table_name 并将 new_table 重命名为 table_name 即可。如果它不起作用..发布错误

另外，请让我们知道show triggers的输出