Solved

The drop primary statement on MySQL

Posted on 2014-03-28
4
227 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
  • 2
4 Comments
 
LVL 34

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql not caching queries 4 63
mysql sql statement - SQL INSERT INTO SELECT 11 59
Insert data into database 2 36
Whether to use true/false, yes/no or 0/1 11 51
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now