Auto_increment Column does not create while updating table query

Question

I create a table whose query is

Create table unititem (ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID));

than alter it by following query

 Alter table unititem AUTO_INCREMENT=1;

I also have a table tablename and in want to alter it by adding a column unitItemId which references table unititem. The query which I want to execute is

Alter table tablename add column Unititemid INT NOT
NULL AUTO_INCREMENT ;

but when I execute this query it gives me this error:

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

When I want to make it a foreign key then it gives 1005 error on following query

Alter table tablename 
add column Unititemid INT NOT NULL AUTO_INCREMENT,
ADD FOREIGN KEY (Unititemid) REFERENCES unititem (ID) ;

I don't know what I'm doing wrong. Any suggestions?

Update1 As suggested I'm not adding unititemid as AUTO_INCREMENT

 ALTER TABLE tablename ADD COLUMN unititemid INT NOT NULL;

Now the problem is: how can I make it a foreign key? When i use the following query it gives an error:

 ALTER TABLE tablename ADD FOREIGN KEY  (unititemid) REFERENCES unititem(ID);

Error: Error Code: 1005 Can't create table 'alnasar_inventory.#sql-a04_1' (errno: 150)
howto resolve this issue ? Any Suggestions.

@jacques sorry i have updated the information kindly take a look again — Naveed Cheema, Sep 22 '17 at 18:37
@jacques i want to use id as a foreign key in another table but i don't know how i have search alot but did not get the job done — Naveed Cheema, Sep 22 '17 at 18:44
@MaxMeijer error is Error Code: 1005 Can't create table 'alnasar_inventory.#sql-a04_1' (errno: 150) — Naveed Cheema, Sep 23 '17 at 05:39
Try looking at the answers in this thread https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error. — Max Meijer, Sep 23 '17 at 07:32
What is the result of running `SHOW CREATE TABLE tablename`? — Max Meijer, Sep 23 '17 at 07:34
Result means ? Did I have to run this query that you mentioned in your coment @maxMeijer — Naveed Cheema, Sep 25 '17 at 02:55
Yes. Result means output / return value. The query shows what kind of table it is and what its columns are. If you post that (or at least the relevant part of it), I might be able to help. — Max Meijer, Sep 25 '17 at 20:24

score 0 · Answer 1 · answered Sep 22 '17 at 18:41

0

To fix the query, simply remove AUTO_INCREMENT. Try:

Alter table tablename add column Unititemid INT NOT
NULL ;

You don't want (and cannot have) the foreign key to auto increment in your new table.

AUTO_INCREMENT exists to automatically generate a new unique id for your table row, but it doesn't make sense to use it on a foreign key.

Consider the table unititem. It has id's 1,2,3. You also have 3 rows in tablename with Unititemid's 1,2,3. Now you create a new row in the other table tablename. How would you expect AUTO_INCREMENT to work here? It would auto increment 3 to 4 but that doesn't make any sense because there is no 4 in unititem. So AUTO_INCREMENT only makes sense on the original table, not on the other table.

answered Sep 22 '17 at 18:41

Max Meijer

1,530
1
14
23

Ok but when i alter table add col Unititemid int not null and after this I make unititemid foreign kry ref unititem id than it gives 1005 error – Naveed Cheema Sep 22 '17 at 18:48
What's the exact query you're trying? – Max Meijer Sep 22 '17 at 18:51
After adding unititemid int not null in tablename than i use the following alter table tablename add foreign key (unititemid) references unititem (ID) but this give error – Naveed Cheema Sep 22 '17 at 18:55
Ok the tablename i'm using has many columns and i did not build tablename that is why i'm altering it and adding this column and it gives error like of same datatype, do you think there would be some restrictions in tablename to add a foreign key – Naveed Cheema Sep 22 '17 at 19:17

Auto_increment Column does not create while updating table query

1 Answers1