?
Solved

The drop primary statement on MySQL

Posted on 2014-03-28
4
Medium Priority
?
249 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 1000 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 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 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