Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

The drop primary statement on MySQL

Dear all,

we have a drop primary key statement for a table:

alter table Insight_20101202_DataAudit.tblSale drop primary key ;   

Open in new window


but the error is:

Can't DROP 'PRIMARY'; check that column/key exists

Open in new window


the schema of the table tblSale is :

CREATE TABLE `tblSale` (
  `ID` int(6) NOT NULL,
  `RFQID` int(11) NOT NULL DEFAULT '0',
  `RFQRequestID` int(11) NOT NULL DEFAULT '0',
  `RFQComponentID` int(11) NOT NULL DEFAULT '0',
  `CircuitID` int(11) NOT NULL DEFAULT '0',
  `CustomerID` varchar(6) NOT NULL DEFAULT '',
  `CircuitPrefix` varchar(6) NOT NULL DEFAULT '',
  `SignedRegion` char(3) NOT NULL DEFAULT '',
  `SignedCo` int(2) NOT NULL DEFAULT '0',
  `PartnerID` varchar(6) NOT NULL DEFAULT '',
  `OffLoc` char(3) NOT NULL DEFAULT '',
  `ProductID` char(3) NOT NULL DEFAULT '',
  `SaleStatusID` char(2) NOT NULL DEFAULT '',
  `InitialContactD` date NOT NULL DEFAULT '0000-00-00',
  `HotProspectD` date NOT NULL DEFAULT '0000-00-00',
  `TarCloseD` date NOT NULL DEFAULT '0000-00-00',
  `SalesCloseD` date NOT NULL DEFAULT '0000-00-00',
  `RenOpt` int(2) NOT NULL DEFAULT '0',
  `RenMon` int(2) NOT NULL DEFAULT '0',
  `ImplStartD` date NOT NULL DEFAULT '0000-00-00',
  `TarOPD` date NOT NULL DEFAULT '0000-00-00',
  `ProjOPD` date NOT NULL DEFAULT '0000-00-00',
  `OPD_Justify` text NOT NULL,
  `OperationD` date NOT NULL DEFAULT '0000-00-00',
  `BillingD` date NOT NULL DEFAULT '0000-00-00',
  `ContractRenD` date NOT NULL DEFAULT '0000-00-00',
  `DropD` date NOT NULL DEFAULT '0000-00-00',
  `TermD` date NOT NULL DEFAULT '0000-00-00',
  `CancelD` date NOT NULL DEFAULT '0000-00-00',
  `OneTimeCommD` date NOT NULL DEFAULT '0000-00-00',
  `FreeTrialD` int(2) NOT NULL DEFAULT '0',
  `BUH_Approval` int(1) NOT NULL DEFAULT '0',
  `Revenue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `GrossMargin` decimal(10,2) NOT NULL DEFAULT '0.00',
  `GMPercent` decimal(10,2) NOT NULL DEFAULT '0.00',
  `OT_Revenue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `OT_GrossMargin` decimal(10,2) NOT NULL DEFAULT '0.00',
  `OT_GMPercent` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Month_GM_NoEquip` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Month_GMP_NoEquip` decimal(10,2) NOT NULL DEFAULT '0.00',
  `EquipCost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesRemark` text NOT NULL,
  `TechRemark` text NOT NULL,
  `SignedBDM` varchar(12) NOT NULL DEFAULT '',
  `SalesBDM` varchar(12) NOT NULL,
  `SalesmanCode` varchar(50) DEFAULT NULL,
  `Engineer` varchar(12) DEFAULT NULL,
  `Engineer_bak3` varchar(12) DEFAULT NULL,
  `Engineer_bak` varchar(12) NOT NULL DEFAULT '',
  `Engineer_bak2` varchar(12) NOT NULL,
  `PrevCircuitID` int(11) DEFAULT NULL,
  `NextCircuitID` int(11) DEFAULT NULL,
  `Ser_Complete` int(1) NOT NULL DEFAULT '0',
  `Dps_Amt` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Dps_AmtCcy` char(3) NOT NULL DEFAULT '',
  `Dps_AmtRec` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Dps_RecDate` date NOT NULL DEFAULT '0000-00-00',
  `Type` int(1) NOT NULL DEFAULT '0',
  `RelocateType` char(10) DEFAULT NULL,
  `LastUpDate` datetime NOT NULL,
  `Days4Billing` int(2) DEFAULT '0',
  `PVType` char(1) NOT NULL DEFAULT '',
  `agent` varchar(50) NOT NULL DEFAULT '',
  `usmarket` char(2) NOT NULL DEFAULT '',
  `qualdate` date NOT NULL DEFAULT '0000-00-00',
  `prodevdate` date NOT NULL DEFAULT '0000-00-00',
  `prodeliverdate` date NOT NULL DEFAULT '0000-00-00',
  `clientrevdate` date NOT NULL DEFAULT '0000-00-00',
  `profbdate` date NOT NULL DEFAULT '0000-00-00',
  `awardnotifydate` date NOT NULL DEFAULT '0000-00-00',
  `ordcrtdate` date NOT NULL DEFAULT '0000-00-00',
  `negcontdate` date NOT NULL DEFAULT '0000-00-00',
  `onhold` int(11) NOT NULL DEFAULT '0',
  `onholddate` date NOT NULL DEFAULT '0000-00-00',
  `linktype` varchar(50) DEFAULT NULL,
  `relatedto` text,
  `billmax_id` int(11) DEFAULT NULL,
  `billplex_id` int(11) DEFAULT NULL,
  `siteserial` int(11) DEFAULT NULL,
  `serviceid` char(2) DEFAULT '',
  `serviceserial` int(11) DEFAULT NULL,
  `VendorCirID` varchar(50) NOT NULL DEFAULT '',
  `ImplAt` int(1) NOT NULL DEFAULT '0',
  `Segment` char(1) NOT NULL DEFAULT '',
  `RelationID` int(1) NOT NULL DEFAULT '0',
  `RelationDetail` varchar(100) NOT NULL DEFAULT '',
  `CompetitorIdentity` varchar(40) NOT NULL DEFAULT '',
  `CompetitorEnvironment` int(1) NOT NULL DEFAULT '0',
  `RelatedDocName` varchar(100) NOT NULL DEFAULT '',
  `UpdatePID` int(1) NOT NULL DEFAULT '0',
  `infoserve` int(11) NOT NULL DEFAULT '0',
  `row_id` int(11) NOT NULL DEFAULT '0',
  `SiteSurveyComp` tinyint(11) DEFAULT NULL,
  `SurveyCompDate` date DEFAULT NULL,
  `ExRate` varchar(20) DEFAULT NULL,
  `NoOfSeat` tinyint(4) DEFAULT NULL,
  `SiteSurveyForm` varchar(100) DEFAULT NULL,
  `Continental_Rate` varchar(50) DEFAULT NULL,
  `Toll_Free_Rate` varchar(50) DEFAULT NULL,
  `Training_Req` int(2) NOT NULL DEFAULT '0',
  `IsTSS` int(1) NOT NULL DEFAULT '0',
  `Remedy_Flag` varchar(4) NOT NULL DEFAULT '',
  `GSMP_Flag` int(1) NOT NULL DEFAULT '0',
  `SR` tinyint(4) DEFAULT NULL,
  `SRcreateDate` varchar(19) DEFAULT NULL,
  `Billing_Platform` char(3) NOT NULL DEFAULT '',
  `SRVerify` int(1) NOT NULL DEFAULT '0',
  `SCN_Complete` tinyint(1) NOT NULL,
  `ProvisionedD` date NOT NULL DEFAULT '0000-00-00',
  `PendingR` text NOT NULL,
  `TCV` decimal(10,2) NOT NULL,
  `CMR_CSVDATE` varchar(8) DEFAULT NULL,
  `ACNO` varchar(100) NOT NULL,
  `ChargeCode` varchar(100) NOT NULL,
  `ORDER_NO` int(11) NOT NULL,
  `Source` varchar(100) NOT NULL,
  `CascadeProv` char(1) DEFAULT NULL,
  `BDM_mapped` int(1) NOT NULL,
  `DGN_CUST_NO` varchar(100) NOT NULL,
  `ISIS_ID` int(11) NOT NULL,
  `ISIS_ServiceType` varchar(100) NOT NULL,
  `ISIS_ServiceCat` varchar(100) NOT NULL,
  `ISIS_CircuitID` varchar(100) NOT NULL,
  `ISIS_CurrencyCode` varchar(100) NOT NULL,
  `ISIS_ExchangeRate` varchar(100) NOT NULL,
  `ISIS_ServiceDescription` varchar(255) NOT NULL,
  `ISIS_ContractLength` varchar(100) NOT NULL,
  `IPVCWFMonthly` decimal(14,2) NOT NULL,
  `IPVCWFOneTime` decimal(14,2) NOT NULL,
  `GroupInvSR` int(11) NOT NULL DEFAULT '0',
  `GnlTnC_Complete` int(1) NOT NULL,
  `PdtTnC_Complete` int(1) NOT NULL,
  `WOA_Complete` int(1) NOT NULL,
  `CustomerRefNo` varchar(20) DEFAULT NULL,
  `PackageID` int(8) DEFAULT NULL,
  `RelPackageID` int(8) DEFAULT NULL,
  `MasterSR` int(6) DEFAULT NULL,
  `EndUserID` varchar(6) DEFAULT NULL,
  `Operation_By` varchar(4) DEFAULT NULL,
  `ExportToSalesReport` int(1) NOT NULL DEFAULT '0',
  `TriggerAction` varchar(10) DEFAULT NULL,
  `ActionDone` char(10) DEFAULT NULL,
  `Action_AuditDate` datetime DEFAULT NULL,
  `ActionBy_Audit` varchar(50) DEFAULT NULL,
  UNIQUE KEY `ID` (`ID`),
  KEY `CustomerID` (`CustomerID`),
  KEY `PartnerID` (`PartnerID`),
  KEY `Status` (`SaleStatusID`),
  KEY `LastUpDate` (`LastUpDate`),
  KEY `MasterSR` (`MasterSR`),
  KEY `RFQID` (`RFQID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


how to handle it ?

or I have to drop the unique key instead ? what is the statement then ?
0
marrowyung
Asked:
marrowyung
  • 2
2 Solutions
 
Dan CraciunIT ConsultantCommented:
As the error says, you do not have a primary key.

If you want to remove the unique key ID, you can do it like this:

DROP INDEX  `ID` ON tblsale

Open in new window

HTH,
Dan
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simple: the table does not HAVE any primary keys, only indexes (keys) and 1 unique key (which does not apply for a "primary" key...
0
 
marrowyungAuthor Commented:
wait, my logic seems see the primary key index in the same way as the unique key, should that be correct ?
0
 
marrowyungAuthor Commented:
my logic is :

select  count(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA =Trigger_databasename AND TABLE_NAME = trigger_tablename and COLUMN_KEY= "PRI"  into HasPrimaryKey;

       IF HasPrimaryKey> 0 THEN 	
   /* set drop_PrimaryKey_statement= CONCAT(' alter table Audit_info.',Trigger_databasename, '_DBAudit_',trigger_tablename,' drop primary key ; 
   alter table Audit_info.',trigger_tablename,' drop primary key ;'); */
   
     set drop_PrimaryKey_statement= CONCAT(' alter table Audit_info.',Trigger_databasename, '_DBAudit_',trigger_tablename,' drop primary key ; '); 
  

Open in new window


so if I see column_key is PRI key when I scan field to field, then I will generate that drop primary key statement, but some times, some database, will use the unique key as primary key.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now