Gossamer Forum
Home : General : Databases and SQL :

Mmmm... primary key problems :(

Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
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
Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!