Duplicate entry error in 1 column of a composite key

Question

I am trying to insert pseudo data into my db to get going, and in one particular table I have two columns which are FK's and PK's of the table; fk_product_manf_code and fk_content_id. To my understanding, these are considered composite keys in their current state.

So I add data to the table:

fk_product_manf_code     fk_content_id
NOV-ABC123               1

I then want to associate another content_id to the same product_manf_code, so I perform the following:

INSERT INTO `mydb`.`package_contents`
(`fk_product_manf_code`, `fk_content_id`)
VALUES
('NOV-ABC123', 2);

However I'm greeted with the following error:

Error Code: 1062. Duplicate entry 'NOV-ABC123' for key 'fk_product_manf_code_UNIQUE'

I don't understand what's going, because I thought a composite key makes 2 columns unique? So why is it kicking up a fuss about just 1 column being unique?

Here is the table CREATE statement

CREATE TABLE `package_contents` (
  `fk_product_manf_code` varchar(255) NOT NULL,
  `fk_content_id` int(11) NOT NULL,
  PRIMARY KEY (`fk_content_id`,`fk_product_manf_code`),
  UNIQUE KEY `fk_content_id_UNIQUE` (`fk_content_id`),
  UNIQUE KEY `fk_product_manf_code_UNIQUE` (`fk_product_manf_code`),
  CONSTRAINT `content_id` FOREIGN KEY (`fk_content_id`) REFERENCES `contents` (`content_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `product_manf_code` FOREIGN KEY (`fk_product_manf_code`) REFERENCES `products` (`product_manf_code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

So, you are learning why composite primary keys are a pain, especially for foreign key constraints. Not only are integer keys more efficient, but a single key is easier to work with.

I would suggest changing your table structure to be more like this:

CREATE TABLE package_contents (
  package_contents_id int not null auto_increment primary key,
  fk_product_manf_id int NOT NULL,
  fk_content_id int(11) NOT NULL,
  UNIQUE KEY (fk_content_id, fk_product_manf_id),
  CONSTRAINT content_id FOREIGN KEY (fk_content_id)
      REFERENCES contents(content_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT product_manf_code FOREIGN KEY (fk_product_manf_id)
      REFERENCES products(product_manf_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note that I changed the manufacturer code to an id as well. This should also reduce the size of the table, assuming that the "code" is longer than 4 bytes.

If you do this for all your tables, the database will be a bit more efficient, and you won't need superfluous unique constraints. The foreign key constraints should always be to primary keys (unless there is a very good reason for using a different unique key).