MySQL error - Overclock.net

F3t1sh · 01-30-08

I have a table with a column called ID which auto increments when i add values but the problem is when I go to delete a row before another row and I try to add another value, I get:
"Error: Duplicate entry '2' for key 1"
So is there a way to make that column auto update to "1" if it starts at "2" and if there are values (doesn't go up like 1, 2, 3, 4, etc) missing?

Starholdest · 01-30-08

I don't think there is. You have your ID row setup like this right:

mirunit · 01-30-08

Set it as an Identity field. In MSSQL I know it is Identity(1,1)

linskingdom · 01-31-08

Make sure the column data type is INT, Select/set Identity property to "Yes". And default seek/increament will be 1/1.

F3t1sh · 01-31-08

Well considering i only have access to phpMyAdmin to work with, how can i do that with only:

Field Type Collation Attributes Null Default Extra Action
ID int(3) ----------------------------No--------- auto_increment

as options available. Anyone know how to do this, or is this done through a SQL Query or through the php

F3t1sh · 01-31-08

would my code be something like:

CREATE TABLE room
(
ID INT(1,1) NOT NULL AUTO_INCREMENT,
)

even then it doesnt work

Starholdest · 02-01-08

Is it set as the primary key?

e_dogg · 02-01-08

Quote:

Originally Posted by F3t1sh

would my code be something like:

CREATE TABLE room
(
ID INT(1,1) NOT NULL AUTO_INCREMENT,
)

even then it doesnt work

You'll need to set the ID column as identitiy as well. I can't remember what the exact syntax for that is in mySQL but I'm pretty sure that if you're creating the table it would be something like:

Code:

CREATE TABLE room
(
ID identity INT(1,1) NOT NULL AUTO_INCREMENT,
)

F3t1sh · 02-03-08

Quote:

Originally Posted by Starholdest

Is it set as the primary key?

Yes it is and still doesn't work

F3t1sh · 02-03-08

Quote:

Originally Posted by e_dogg

Code:

CREATE TABLE room
(
ID identity INT(1,1) NOT NULL AUTO_INCREMENT,
)

I get:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identity INT(1,1) NOT NULL AUTO_INCREMENT,
)' at line 3
(0 ms taken)

I guess my real problem is reseting the ID row and making it auto number itself again when i delete something.

		Overclock.net - Overclocking.net > Software, Programming and Coding > Coding and Programming > Web Coding
MySQL error

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)