mysql - Adding column to existing unique key? -

i have below table created in mysql.

 create table `postitem` (   `id` bigint(20) not null auto_increment,   `createdat` datetime default null,   `item_name` varchar(255) not null,   `createdby_id` bigint(20) not null,   `post_category_id` bigint(20) not null,   primary key (`id`),   unique key `uk_4cscn0qw8u7go5hvmofq0ersg` (`post_category_id`,`item_name`),   key `fk_ncg8dotnqoetaiem6hiokvpwy` (`createdby_id`),   constraint `fk_ncg8dotnqoetaiem6hiokvpwy` foreign key (`createdby_id`) referen                                                                                                                     ces `applicationuser` (`id`),   constraint `fk_nfh1xw0eqqu9wg5hhl7iqdk56` foreign key (`post_category_id`) ref                                                                                                                     erences `postcategory` (`id`) ) engine=innodb auto_increment=737 default charset=utf8 | 

now need add 1 more column createdby_id unique key. how can that?

you can drop existing constraint, , recreate new fields. suggest renaming more appropriate @ same time:

alter table `postitem` drop index `uk_4cscn0qw8u7go5hvmofq0ersg`; alter table `postitem` add constraint `uk_post_item_createdby`     unique (`post_category_id`,`item_name`, `createdby_id`); 

edit: re foreign key constraints

since changing uniqueness of constraint, tables referencing postitem table via above ukc need remodelled accommodate new column createdby_id well. each such referencing table, need to

• drop foreign key constraint
• add new createdby_id column (same type)

now can

• drop unique key constraint on postitem
• add new unique key constraint on postitem column

and again each referencing table

• add new foreign key constraint referencing postitem(post_category_id, item_name, createdby_id))

however, @ point suggest rethink table design here. have single primary key id on postitem table, imo better candidate foreign key constraints reference. i.e. suggest instead, you:

• drop foreign key constraints on referencing tables above
• drop columns post_category_id , item_name in these tables
• instead, add new column postitemid bigint(20) these tables
• create new foreign key constraint othertable.postitemid postitem(id)

obviously, may have massive impact on rest of system.

one of main benefits of simple surrogate keys (like auto_increment ints) more resilient change, guess we've proven here.