Reputation: 131
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:
There aren't two auto incremented columns in subcategory table, only the 'subCatId' is. What should be done?
Upvotes: 0
Views: 413
Reputation: 142298
You have
Constraint pk_category
PRIMARY KEY(catId)
Instead, just say
PRIMARY KEY(catId)
Upvotes: 1
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.
subCatId
as Primary Key
Unique
Upvotes: 1
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
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