Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    11

    Unanswered: Primary Key issues??

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    11
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SilverJester View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    11
    Quote Originally Posted by r937 View Post
    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 View Post
    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 View Post
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SilverJester View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2009
    Posts
    11
    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
    Last edited by SilverJester; 02-26-11 at 15:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •