CODEWITHSUNDEEP
mysql
Hailwood
Hailwood

Reputation: 92651

Column is not autoincrementing in mysql

When I insert a new row the treeId column is always 1.
The treeId column is not included in the insert statement.

What could be causing it to not increment?

My table code is 

CREATE TABLE `users` (
  `uuid` varchar(36) NOT NULL,
  `parentUuid` varchar(36) DEFAULT NULL,
  `treePath` text,
  `treeId` int(11) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `salt` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `state` enum('subscribed','registered','banned') NOT NULL,
  `dobMonth` int(11) DEFAULT NULL,
  `dobYear` int(11) DEFAULT NULL,
  `dateSubscribed` datetime DEFAULT NULL,
  `dateRegistered` datetime DEFAULT NULL,
  `gender` enum('unspecified','male','female') NOT NULL DEFAULT 'unspecified',
  `dd` float DEFAULT '0',
  `mainRegion` int(11) DEFAULT NULL,
  PRIMARY KEY (`uuid`,`treeId`),
  KEY `parentid` (`parentUuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Upvotes: 0

Views: 59

Answers (2)

Hailwood
Hailwood

Reputation: 92651

Found the answer (Documentation)

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |

So because I had a joint key on uuid (which is always unique) and on treeId (which is the auto-increment) then it was creating a new increment group each time.

Upvotes: 0

Karoly Horvath
Karoly Horvath

Reputation: 96276

if you want it to auto-increment don't specify the treeId field in your insert.

Upvotes: 1

Related Questions