phpmyadmin Question posted in
The official documentation can be found here.

phpmyadmin shows error double increment with two column not possible

So I tried to make these following two tables with phpmyadmin.

create table category (
catId int identity(1,1),
catName varchar(20),

Constraint pk_category
PRIMARY KEY(catId))

create table subcategory (
subCatId INT IDENTITY(1,1),
catId INT,
subCatName VARCHAR(20),

CONSTRAINT pk_subcategory
PRIMARY KEY(catId,subCatId),

CONSTRAINT fk_subcat_cat
FOREIGN KEY(catID)
REFERENCES category(catId))

When creating the subcategory it shows this query error:

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

There aren’t two auto incremented columns in subcategory table, only the ‘subCatId’ is. What should be done?

Tags:

4

Answers


  1. 0

    You appear to be using SQL Server syntax, but run against MySQL. Use the correct syntax and it should work:

    CREATE TABLE category (
    catId INT NOT NULL AUTO_INCREMENT,
    catName VARCHAR(20),
    PRIMARY KEY (catId)
)

CREATE TABLE subcategory (
    subCatId INT NOT NULL AUTO_INCREMENT,
    catId INT,
    subCatName VARCHAR(20),
    FOREIGN KEY (catId) REFERENCES category (catId),
    PRIMARY KEY (subCatId)
);

    IDENTITY in SQL Server roughly corresponds to AUTO_INCREMENT in MySQL.

    Login or Signup to reply.
  2. 0

    It is only available for the MyISAM Storage Engine Only one numeric
    auto_increment value is allowed. Each auto_increment must be have
    associated column to define uniqueness from other auto_increment
    values within the same table.

    This ref link may hep you more about this.

    Note:
    Don’t do that, don’t have a second auto incremented column at all. Do you really need a second auto incremented value? What for? A
    description of the actual problem you are trying to solve would help
    others help you better. I think you have only told here how you tried
    to solve a problem and not what the actual problem is.

    Login or Signup to reply.
  3. 0

    This is what MySQL states

    There can be only one AUTO_INCREMENT column per table, it must be
    indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column
    works properly only if it contains only positive values. Inserting a
    negative number is regarded as inserting a very large positive number.
    This is done to avoid precision problems when numbers “wrap” over from
    positive to negative and also to ensure that you do not accidentally
    get an AUTO_INCREMENT column that contains 0.

    So according to your requirement you’ve following choices.

    • Make subCatId as Primary Key
    • Or Make the column as Unique
    Login or Signup to reply.
  4. 0

    You have

    Constraint pk_category
PRIMARY KEY(catId)

    Instead, just say

    PRIMARY KEY(catId)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search