Auto_increment in child tables

Question

I'm trying to create a table to capture details of subtype variations using auto_increment but I keep get an error saying:

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

However, I'm unsure how to capture it all correctly as I want the auto_increment of subtypes to start from 1 for each TypeId....is this not possible?

CREATE TABLE IF NOT EXISTS `types` (
  `TypeID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `TypeFeatures` TEXT NOT NULL,
  PRIMARY KEY (`TypeID`))
ENGINE = InnoDB;


insert into types 
    (TypeFeatures)
values
    (1,'Type1'),
    (2,'Type2'),
    (3,'Type3')
;

  
CREATE TABLE IF NOT EXISTS `subtypes` (
  `Type_ID` INT UNSIGNED NOT NULL,
  `SubtypeID` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `SubtypeFeatures` TEXT NOT NULL,
  PRIMARY KEY(`Type_ID`,`SubtypeID`))
ENGINE = InnoDB;

insert into subtypes 
    (Type_ID,subtypeID,SubtypeFeatures)
values
    (1,1,'Subtype1'),
    (1,2,'Subtype2'),
    (2,1,'Subtype1'),
    (3,1,'Subtype1')
;

Thanks in advance for any suggestions! Bendy

I'm getting this: `#1136 - Column count doesn't match value count at row 1` because you are inserting 2 values to only 1 column: TypeFeatures — Unamata Sanatarai, Sep 28 '14 at 21:08
possible duplicate of [MySQL: #1075 - Incorrect table definition; autoincrement vs another key?](http://stackoverflow.com/questions/8114535/mysql-1075-incorrect-table-definition-autoincrement-vs-another-key) — Unamata Sanatarai, Sep 28 '14 at 21:14
please see the answer to this question here: http://stackoverflow.com/a/8114994/2119863 — Unamata Sanatarai, Sep 28 '14 at 21:14

Auto_increment in child tables

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

0 Answers0