0

Can I add two rows auto increment in two ids of same column

Let suppose I have an ID as Primary key and CID as secondary Key How I can add Auto increment function on both ids is there any solution in phpMYadmin using MySQL database For Example I want this is it possible???

CREATE TABLE IF NOT EXISTS `tbl_project_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Key` (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

--
-- Dumping data for table `tbl_project_category`
--
Arslan Ali
  • 77
  • 1
  • 10
  • Check out [http://stackoverflow.com/questions/5665571/auto-increment-in-phpmyadmin](http://stackoverflow.com/questions/5665571/auto-increment-in-phpmyadmin). Hope it helps. – Singmyr Jan 16 '15 at 11:48
  • 1
    @MattiasValpenSingmyr I want auto increment in two ids ?? – Arslan Ali Jan 16 '15 at 11:49
  • It is possible BUT why would you have an AI secondary key if you can use the PK fine.... if you use a secondary key it's most likely to be a FK – Mike M. Jan 16 '15 at 11:49
  • @MikeM. I updated the Question I want same I mentioned.. is it possible – Arslan Ali Jan 16 '15 at 11:54
  • I got this error when i select two AI #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key – Arslan Ali Jan 16 '15 at 11:56
  • @MarkBaker I want it have you any idea – Arslan Ali Jan 16 '15 at 11:57
  • 1
    Why do you want it? Given that both columns would have the same value anyway, what's the point? – Mark Baker Jan 16 '15 at 11:58
  • or you have any solution to set id = cid @MattiasSingmyr – Arslan Ali Jan 16 '15 at 11:59
  • One possible way of doing this is to have an ON INSERT trigger that populates the second `ai` column with the value of the first `id` column – Mark Baker Jan 16 '15 at 12:00
  • @MarkBaker or you have any solution to set id = cid – Arslan Ali Jan 16 '15 at 12:00
  • Will you please guide me how I can do this @MarkBaker – Arslan Ali Jan 16 '15 at 12:01
  • Define your `cid` column as a standard `int(11) not null` (without the autoincrement), then something like: `CREATE TRIGGER ins_cid BEFORE INSERT ON tbl_project_category FOR EACH ROW SET @cid = @id;` – Mark Baker Jan 16 '15 at 12:03
  • @MarkBaker Nothing happens :( – Arslan Ali Jan 16 '15 at 12:10
  • Try `CREATE TRIGGER ins_cid BEFORE INSERT ON tbl_project_category FOR EACH ROW SET NEW.cid = NEW.id;` – Mark Baker Jan 16 '15 at 12:11
  • Though I still don't understand why you actually need this at all – Mark Baker Jan 16 '15 at 12:16
  • I want to define cid for Category page with isset cid and id for product page to show products @MarkBaker – Arslan Ali Jan 16 '15 at 12:18
  • 1
    This really smells like very bad database design.... if this is a cross reference table, then it should have a single autoincrement id for uniqueness, and then foreign keys `product_id` and `category_id` referencing the `id` values in your `products` and `categories` tables respectively – Mark Baker Jan 16 '15 at 12:28
  • assuming `CID` would be categoryid there should be a category table with an `ID` as a project **HAVE** to be in one of the desired categories in that table, you'd have to work with a `FOREIGN KEY CONSTRAINT`. If you do that, you're always secured that the `CID` is with one of the categories – Mike M. Jan 18 '15 at 16:56

0 Answers0