Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
ASKER CERTIFIED SOLUTION
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

wait, my logic seems see the primary key index in the same way as the unique key, should that be correct ?
Avatar of marrowyung
marrowyung

ASKER

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.