• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

add MySQL primary key

dear all,

right now have structure like this of a table :

CREATE TABLE `tblInventory` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `Parent` int(11) DEFAULT NULL,
  `SNMPName` varchar(30) DEFAULT NULL,
  `Make` varchar(100) DEFAULT NULL,
  `Model` varchar(100) DEFAULT NULL,
  `Part` varchar(30) DEFAULT NULL,
  `Serial` varchar(20) DEFAULT NULL,
  `VendorCircuitID` varchar(100) DEFAULT NULL,
  `VendorID` int(11) DEFAULT NULL,
  `DataRateSDH` char(1) NOT NULL DEFAULT '0',
  `DataRateType` varchar(10) DEFAULT NULL,
  `DataRate` decimal(11,3) DEFAULT NULL,
  `DataRateUnit` char(4) DEFAULT NULL,
  `DataRateVC12` bigint(20) unsigned DEFAULT '0',
  `RestorationUse` tinyint(1) DEFAULT '0',
  `UnderInstallation` tinyint(1) DEFAULT '0',
  `TriggerAction_Audit` varchar(10) DEFAULT NULL,
  `ActionDone_Audit` char(10) DEFAULT NULL,
  `ActionTime_Audit` datetime DEFAULT NULL,
  `ActionByHost_Audit` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


and now we want to add one more column to this talbe:

RecordVersion_Audit BIGINT NOT NULL AUTO_INCREMENT; 

Open in new window


however, we need to define that column as a key, but when I do this:

ALTER TABLE xxx.yyy ADD RecordVersion_Audit BIGINT NOT NULL primary key;

it report:

Lookup Error - MySQL Database Error: Duplicate entry '0' for key 'PRIMARY'

Open in new window


any way to fix this ?
0
marrowyung
Asked:
marrowyung
  • 5
1 Solution
 
marrowyungAuthor Commented:
can I just do this :

ALTER TABLE xxx.yyy ADD RecordVersion_Audit BIGINT NOT NULL AUTO_INCREMENT UNIQUE;  

Open in new window

?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error tells you that you already HAVE several records with the value of "0", adding the field with auto_increment will not populate the field as if you had inserted the records after having that field set;

to solve this, you have several ways, the most simple shall be to copy the data to another table, delete it from this table, and reinsert back from the backup table.
0
 
marrowyungAuthor Commented:
"HAVE several records with the value of "0", "

but why relate to the primary key/key ? that's why I don't understand.

" if you had inserted the records after having that field set;
"

you mean this field will not have the auto increment effect for old record?

"to solve this, you have several ways, the most simple shall be to copy the data to another table, delete it from this table, and reinsert back from the backup table. "

I solve that by one statement I post above, near that version but not exactly .
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
marrowyungAuthor Commented:
tks anyway even I fix it last night.

I fix it BASICALLY by trial and error. AHHAHAH
0
 
Dany BalianCTOCommented:
You can also add the field as non primary key.. Update the table using a function, and then set the pkey fields again!

Something like update xxx set recordversion_audit = getnextpkey() where recordversion_audit=0

And getnextpkey would be a function that returns the max+1 from same table xxx

Just saying!
0
 
marrowyungAuthor Commented:
I just remove the field and add it back using a statement VERY LIKE the one in my update, and it just work, confirmed by developer.

that's  simple enough.
0
 
marrowyungAuthor Commented:
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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