0

I try to "alter Table" I need one more AI field, not key... "List"

ID INT(11):PK Not Null AutoIn..
Name VARCHAR
UserID INT(11):FK Not Null
edit BOOL

and now i need one more field "sortpos" as AI. I try it with MySQL Workbench

ALTER TABLE `**mydb**`.`List` 
ADD COLUMN `sortpos` INT(11) NOT NULL AUTO_INCREMENT AFTER `edit`;

Can u help me?

Thx

Akdes
  • 39
  • 1
  • 8
  • as the error says, it must be part of the pkey. see also http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment – koriander Mar 12 '14 at 22:48
  • but I need sortpos individually, like id too, so auto increment. Is there the possibility to clone id in sortpos, default as example? – Akdes Mar 12 '14 at 23:53
  • some explanation is missing, why do you need two fields which have exactly the same value? – koriander Mar 13 '14 at 07:31
  • i need one more fields to sort the output, i need an unique key to switch – Akdes Mar 13 '14 at 20:40
  • I don't follow, you already have a unique key, ID. What do you mean "to switch"? switch what? can you post the query? – koriander Mar 13 '14 at 21:32
  • i dont have now a query.. output as example: 1. First (sortpos 1) Buttons: up/down 2. Second (sortpos 2) Buttons.. 3. ... (sortpos 3) Buttons.. if user click at "up" by 3. than i search for sortpos < 3 order by sortpos DESC LIMIT 1. So now, i know, which data i need to switch, my sortpos from 3. with 2. sry for my (no)-englisch – Akdes Mar 13 '14 at 22:42
  • I would suggest that you put this example in your question, for readability. See my answer for more details. – koriander Mar 14 '14 at 06:42

2 Answers2

1

You can't get better error message than this one. You already have ID defined as Auto Increment in your table. Now you are trying to add another field sortpos as auto increment which is not allowed. One table can only have one auto increment which must be defined as primary key.

Remove AUTO_INCREMENT from the alter statement and create a trigger to increment the new column.

Riz
  • 1,119
  • 15
  • 23
0

Based on your comments, you are confusing user interface with table data. The table only needs to have one ID, if you want you can create a query like this:

SELECT ID, ID AS SORTPOS, NAME FROM List

But you don't even need a query for that, you should do it only at user interface level.

Plus, what you show in your comment is merely the heading of a list, not the list itself.

koriander
  • 3,110
  • 2
  • 15
  • 23