LizardKingLK
LizardKingLK

Reputation: 131

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?

Upvotes: 0

Views: 413

Answers (4)

Rick James
Rick James

Reputation: 142298

You have

Constraint pk_category
PRIMARY KEY(catId)

Instead, just say

PRIMARY KEY(catId)

Upvotes: 1

Kaushik
Kaushik

Reputation: 2090

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

Upvotes: 1

Pragyan
Pragyan

Reputation: 61

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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.

Upvotes: 1

Related Questions