Gossamer Forum: General: Databases and SQL: Mmmm... primary key problems :(

May 11, 2005, 9:44 AM

Andy

Veteran / Moderator (17297 posts)

May 11, 2005, 9:44 AM

Post #1 of 3

Shortcut

Mmmm... primary key problems :(

Hi,

I've got a really weird problem with a custom MySQL table.

A MySQLMan dump of the table in question looks like;

Code:
# 
# Table structure for table 'Categories' 
# 
CREATE TABLE Categories ( 
  Full_Name text    , 
  Count int(11)    , 
  CatID tinyint(11)  DEFAULT '0' NOT NULL , 
  FatherID int(11)    , 
  UNIQUE CatID (CatID) 
); 

# 
# Dumping data for table 'Categories' 
# 

INSERT  INTO Categories (Full_Name,Count,CatID,FatherID) VALUES('Art',NULL,'0','0'); 
INSERT  INTO Categories (Full_Name,Count,CatID,FatherID) VALUES('Art/Photography',NULL,'127','0');

As you can see, there are only 2 entries in there.

Now, if I try to run the following command;

Code:
INSERT INTO Categories (`Full_Name`,`CatID`,`FatherID`) VALUES ('Art/Photography/Photography Subjects',56200,0);

..I get the following error message;

Quote:

Error

MySQL said: Duplicate entry '127' for key 1.

Query: INSERT INTO Categories (`Full_Name`,`CatID`,`FatherID`) VALUES ('Art/Photography/Photography Subjects',56200,0)

I just can't seem to get it to work. There has to be something silly/small that I 'm missing Frown

TIA

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

May 11, 2005, 12:31 PM

Chaz

Enthusiast (819 posts)

May 11, 2005, 12:31 PM

Post #2 of 3

Shortcut

Re: [Andy] Mmmm... primary key problems :( In reply to

Check the MySQL docs for TINYINT[1]. The limit is 127 (for signed that is):

Quote:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

Your 56200 is being converted to 127. If I had to guess, the CatID for Art/Photography isn't 127 either :) Try an unsigned INT type instead.

~Charlie

[1] http://dev.mysql.com/...c-type-overview.html

May 12, 2005, 12:38 AM

Andy

Veteran / Moderator (17297 posts)

May 12, 2005, 12:38 AM

Post #3 of 3

Shortcut

Re: [Chaz] Mmmm... primary key problems :( In reply to

Hi,

You star! Changed CatID to an INT, and it works perfectly <G> Now to try and get this script finished off ... fun fun Unimpressed

Thanks again.

Andy (mod)
andy@ultranerds.co.uk