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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
can I just do this :

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

Open in new window

?
0
 
marrowyungSenior Technical architecture (Data)Author 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 expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.