Foreign key problem

[samikhan83]

hi...
i am having a problem assigning a foreign to a table....

i have two tables product table which is having two primary key and quantity table which have quant_poduct_upc_code

1. product_id which is of integer type and auto increment

2. product_upc_code which is of varchar type with size 40

what i want to do is reference product_upc_code of product with the quantity product_upc_code

both the fields are of same type and size i.e. varchar(40) but mysql is throwing an error saying error 1005 which is index not found...

is this anything to do with the two primary keys in one table.......

can anybody help me its URGENT.....

Thanx in Adavance..

[Atli]

Hi.

Might be just me, but I didn't really understand your explanation of you table structure.
Could you post the CREATE statements you are using?

[samikhan83]

hi...

this is the product table

 
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `p_id` int(10) unsigned NOT NULL auto_increment,
  `p_manufacturer` varchar(60) NOT NULL,
  `p_name` varchar(60) NOT NULL,
  `p_upc_code` varchar(40) NOT NULL,
  `p_model_number` varchar(40) NOT NULL,
  `p_description` varchar(150) NOT NULL,
  `p_price_a` int(10) unsigned NOT NULL,
  `p_price_b` int(10) unsigned NOT NULL,
  `p_price_c` int(10) unsigned NOT NULL,
  `p_serial_number` varchar(5) NOT NULL,
  `p_status` varchar(20) NOT NULL default 'ACTIVATED',
  `p_type_id` int(10) unsigned NOT NULL,
  `p_group_id` int(10) unsigned NOT NULL,
  `p_category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`p_id`,`p_upc_code`),
  KEY `FK_product_1` (`p_type_id`),
  KEY `FK_product_2` (`p_group_id`),
  KEY `FK_product_3` (`p_category_id`),
  CONSTRAINT `FK_product_1` FOREIGN KEY (`p_type_id`) REFERENCES `product_type` (`p_t_id`),
  CONSTRAINT `FK_product_2` FOREIGN KEY (`p_group_id`) REFERENCES `product_group` (`p_g_id`),
  CONSTRAINT `FK_product_3` FOREIGN KEY (`p_category_id`) REFERENCES `category_group` (`cat_grp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
 
 

this is second table

where i can't make relation with the UPC_CODE field of product with the UPC_CODE field of this table

 
DROP TABLE IF EXISTS `quantity`;
CREATE TABLE `quantity` (
  `quant_id` int(10) unsigned NOT NULL auto_increment,
  `quant_upc_code` varchar(40) NOT NULL,
  `quant` int(10) unsigned NOT NULL,
  `quant_date` datetime NOT NULL,
  `quant_timestamp` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`quant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;
 
 
 

[coolsti]

Do you really want to have the two-column primary key in your first table?

Firstly, your first column is integer auto_increment and will be unique, so this alone is sufficient to serve as a primary key, and also as a unique foreign key to other tables.

Secondly, your second column in your primary key is of type varchar(40). This makes for a far less efficient index in general (since it is of type varchar which is text, is longer and is of variable length).

I would change things so that the primary key in the first table is only the first column. If you need an index on the varchar field to speed up queries, you could also add that.

[Atli]

I would have to agree with that.

There is no point having a joined Primary Key unless you want to be able to create multiple rows with the same p_upc_code.

I would make the p_id the Primary Key and the p_upc_code Unique, and then reference the p_id in the second table, rather than the upc_code, just to save space.