Primary Key issues??

SilverJester · #1 (**permalink**) 02-24-11, 16:09

I posted a thread in the php but after some more investigating it seems that my problem is more of a database issue. I have a 2 tables in a database ("products" and "apparel"). The products table has a composite key composed of "products.Item_Num" and "products.Color". The apparel table should also be a composite key composed of "apparel.Item_Num" and "apparel.Color" but this is where I'm having issues. My php will insert records into the products table without problem, however insertion into the apparel table is a problem.

Both tables already contain records with and 'Item_Num' of "abc" and various different colors. But whenever I enter a new record with Item_Num of "abc" and a new different color (ex. brown) I get this error:

1062: Duplicate entry 'abc-brown' for key 'PRIMARY'

I have tried dropping the PRIMARY key and then I get no errors on insertion, but every time I recreate the key I get an error. Despite these errors the record is inserted as it should be. Also, if the Item_Num is unique (by itself without the color aka basically non composite) then I get no errors.

And lastly the apparel.Item_Num should be a foreign key which references products.Item_Num (unless it's possible to have the whole composite key in the apparel table, reference the whole composite key in the products table). I did have the former foreign key constraint, but dropped it after a little while to see if it would help my situation (it didn't).

Thanks in advanced for the help!

r937 · #2 (**permalink**) 02-24-11, 17:17

please do a SHOW CREATE TABLE for both tables

SilverJester · #3 (**permalink**) 02-24-11, 17:48

apparel table:

CREATE TABLE `apparel` (
`Item_Num` char(25) NOT NULL,
`Color` varchar(20) NOT NULL,
`Type` char(1) NOT NULL COMMENT 'Shorts, Tee, Replica, Warmup, socKs, Gk, Underwear, Hats',
`Size2xs` int(11) NOT NULL DEFAULT '0',
`Sizexs` int(11) NOT NULL DEFAULT '0',
`Sizes` int(11) NOT NULL DEFAULT '0',
`Sizem` int(11) NOT NULL DEFAULT '0',
`Sizel` int(11) NOT NULL DEFAULT '0',
`Sizexl` int(11) NOT NULL DEFAULT '0',
`Size2xl` int(11) NOT NULL DEFAULT '0',
`Size3xl` int(11) NOT NULL DEFAULT '0',
`Size4xl` int(11) NOT NULL DEFAULT '0',
`SizeFitsAll` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Item_Num`,`Color`),
KEY `Type` (`Type`),
KEY `itemnum_index` (`Item_Num`),
CONSTRAINT `apparel_ibfk_1` FOREIGN KEY (`Item_Num`) REFERENCES `products` (`Item_Num`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

products table:

CREATE TABLE `products` (
`Item_Num` char(35) NOT NULL,
`Color` varchar(20) NOT NULL,
`Name` char(35) NOT NULL,
`Brand` char(25) NOT NULL,
`Price` decimal(7,2) NOT NULL,
`Sale_Price` decimal(7,2) DEFAULT NULL,
`Cost` decimal(7,2) NOT NULL,
`Date_Added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Gender` char(1) NOT NULL COMMENT 'Y, M, F, or B',
`Shoes` tinyint(1) DEFAULT NULL,
`Apparel` tinyint(1) DEFAULT NULL,
`Equipment` tinyint(1) DEFAULT NULL,
`GKGloves` tinyint(1) DEFAULT NULL,
`Sold_Out` tinyint(1) NOT NULL DEFAULT '0',
`Picture` char(20) DEFAULT 'default.gif',
PRIMARY KEY (`Item_Num`,`Color`),
KEY `Date_Added` (`Date_Added`),
KEY `Gender` (`Gender`),
KEY `Prod_Attrib` (`Brand`,`Price`,`Sale_Price`,`Date_Added`,`Gender `,`Shoes`,`Apparel`,`Equipment`),
KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

r937 · #4 (**permalink**) 02-24-11, 19:03

Quote:

Originally Posted by SilverJester

And lastly the apparel.Item_Num should be a foreign key which references products.Item_Num (unless it's possible to have the whole composite key in the apparel table, reference the whole composite key in the products table).

you cannot make apparel.Item_Num a foreign key to products.Item_Num because products.Item_Num isn't unique

yes, it's possible to have the whole composite key in the apparel table, reference the whole composite key in the products table, however, the columns must match exactly, and you have one of them as CHAR(25) and the other as CHAR(35)

i'm not sure of the cause of your dupes, unless, of course, it's the obvious reason, which is that there already exists an 'abc'/'brown' row in the target table

SilverJester · #5 (**permalink**) 02-26-11, 11:23

Quote:

Originally Posted by r937

you cannot make apparel.Item_Num a foreign key to products.Item_Num because products.Item_Num isn't unique

This makes sense, I had changed the database around a bit and didn't think of this. Thanks.

Quote:

Originally Posted by r937

yes, it's possible to have the whole composite key in the apparel table, reference the whole composite key in the products table, however, the columns must match exactly, and you have one of them as CHAR(25) and the other as CHAR(35)

Good catch, they should both be CHAR(35). I have fixed that but still unsure how to make the composite key reference. Can you please explain how to do that?

Quote:

Originally Posted by r937

i'm not sure of the cause of your dupes, unless, of course, it's the obvious reason, which is that there already exists an 'abc'/'brown' row in the target table

No it's a strange issue (maybe having something to do with your first point about referencing a non unique key) but it's not the obvious reason.

r937 · #6 (**permalink**) 02-26-11, 12:13

Quote:

Originally Posted by SilverJester

... but still unsure how to make the composite key reference. Can you please explain how to do that?

Code:

CREATE TABLE parents
( foo INTEGER NOT NULL
, bar INTEGER NOT NULL
, PRIMARY KEY ( foo,bar )
, stuff VARCHAR(9)
);

CREATE TABLE childs
( foo INTEGER NOT NULL
, bar INTEGER NOT NULL
, qux SMALLINT NOT NULL
, PRIMARY KEY ( foo,bar,qux )
, CONSTRAINT parents_fk
    FOREIGN KEY ( foo,bar )
      REFERENCES parents ( foo,bar )
, stuff VARCHAR(37)
);

let me know if this didn't make sense

SilverJester · #7 (**permalink**) 02-26-11, 14:26

Thanks for your help r937. I got it working (after tracking down a few tuples that contradicted the foreign key constraint), but it's working great now. I believe my original problem was that I had apparel.Item_Num as a foreign key to products.Item_Num even though products.Item_Num wasn't unique and mysql allowed this but produced some odd behavior. Thanks a lot

Internet Services

Web Development

Digital Marketing

Consumer Tech