Solved

The drop primary statement on MySQL

Posted on 2014-03-28
4
246 Views
Last Modified: 2014-06-25
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
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 250 total points
ID: 39961152
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39961947
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39968940
wait, my logic seems see the primary key index in the same way as the unique key, should that be correct ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39968949
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question