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

May 11, 2005, 9:44 AM

Andy

Veteran / Moderator (18436 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

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 (18436 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