CODEWITHSUNDEEP
mysqlsql
Dan Hastings
Dan Hastings

Reputation: 3280

Add an Auto increment column that isnt a primary key SQL

I have a table that keeps track of files on a disk. I have the file path set as the primary key since this is the unique piece of data. I want to add a new column that is auto increment but NOT the primary key. Can this be done? I want this column so i can access the data with an id rather than a big long file path string.

ALTER TABLE `Media`  ADD `ID` INT NOT NULL AUTO_INCREMENT

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

Upvotes: 0

Views: 663

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Change the table structure so the file path is declared to be unique instead of primary. Then add an auto-incrementing primary key.

ALTER TABLE `Media` DROP PRIMARY KEY, 
  ADD UNIQUE KEY (`FilePath`), 
  ADD `ID` INT AUTO_INCREMENT FIRST, 
  ADD PRIMARY KEY (`ID`);

There are advantages to having an integer primary key instead of a string. For instance, a secondary index will use the primary key to access the row, so having a full file path just makes the index larger than it needs to be.

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425251

ALTER TABLE
        `Media`
ADD     `ID` INT NOT NULL UNIQUE AUTO_INCREMENT

Upvotes: 1

akbarian
akbarian

Reputation: 51

you must insert rows like this :

SET @ID = 0;

INSERT INTO Media  (ID,  SomeColumn) VALUES  (@ID := @ID + 1, 'Some Text');

Upvotes: -1

Related Questions