How to add Primary Key and Auto Increment on different fields of MySql

 Normal code generated by MySql:-

CREATE TABLE `categorymaster` (

  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,

  `Category` varchar(200) DEFAULT NULL,

  `IsActiv` int(11) DEFAULT NULL,

  `ImagePath` varchar(200) DEFAULT NULL,

  `DisplayOrder` int(11) DEFAULT NULL,

  PRIMARY KEY (`Category`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the above data stucture I want to make the "Category" as Primary Key and "CategoryId" as Auto Increment.

But Table Wizard always give errors so, I decided to write my own Statement.

Following is the statement which will create perfect structure according to my requirement.

DROP TABLE `dbname`.`categorymaster`;

CREATE TABLE `categorymaster` (

  `CategoryId` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `Category` varchar(200) DEFAULT NULL,

  `IsActiv` int(11) DEFAULT NULL,

  `ImagePath` varchar(200) DEFAULT NULL,

  `DisplayOrder` int(11) DEFAULT NULL,

  UNIQUE KEY `CategoryId` (`CategoryId`),

   PRIMARY KEY (`Category`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Share: