MySQL Forums
Forum List  »  Quality Assurance

Composite PRIMARY KEY creation problem
Posted by: alanmillo alanmillo
Date: April 11, 2011 11:24AM

Hello,

I´m trying to alter a table an add a composite PRIMARY KEY based on two UNIQUE fields, something like the following:

This is the output of the show create table:
CREATE TABLE `user_subscriptions_test` (
`id` int(10) unsigned NOT NULL COMMENT 'Common user id',
`lang` char(5) NOT NULL COMMENT 'Selected subscription language',
UNIQUE (`id_common_user`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


ALTER TABLE user_subscriptions_test
ADD `id_quick_subs_user` INT(10) UNSIGNED DEFAULT NULL,
ADD `email` VARBINARY(100) NOT NULL,
ADD `name` VARCHAR(100) NOT NULL,
ADD UNIQUE (`id_quick_subs_user`),
ADD PRIMARY KEY `id_subs` (`id`, `id_quick_subs_user`);

The table has values already inserted, of course non of the values are equals, but once the alter is thrown, there are expected Null values in one of the keys.

When I execute the alter query the following error is trhown:
- ERROR 1062 (23000): Duplicate entry '0' for key 'id_quick_subs_user'

I must say the table do not have any '0' entry.

Is that normal? As far as I know and found in the documentation, the composite primary key can´t be of course NULL, but is it not suppose to be NULL only if both columns are NULL?

This error is driving me crazy :(, hope you have a suitable answer :)

Thank you very much.

Options: ReplyQuote


Subject
Views
Written By
Posted
Composite PRIMARY KEY creation problem
3844
April 11, 2011 11:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.