Adding column to existing unique key?

Question

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 I need to add one more column createdBy_id to UNIQUE KEY. How can I do that?

Answer 1

You can drop the existing constraint, and then recreate it with the new fields. I suggest renaming it to something more appropriate at the 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 you are changing the uniqueness of the constraint, all tables which are referencing your postitem table via the above UKC will now need to be remodelled to accommodate the new column createdBy_id as well. So for each such referencing table, you will need to

• Drop the FOREIGN KEY constraint
• Add the new createdBy_id column (same type)

Now you can

• Drop the Unique Key constraint on postitem
• Add the new Unique Key constraint on postitem with the extra column

And again for each referencing table

• Add a new foreign key constraint referencing postitem(post_category_id, item_name, createdBy_id))

However, at this point I would suggest that you rethink your table design here. You have a single primary key id on the postitem table, which IMO would generally be a better candidate for foreign key constraints to reference. i.e. I would suggest that instead, you:

• DROP the foreign key constraints on referencing tables as above
• DROP the columns post_category_id and item_name in these tables
• Instead, ADD a new Column postitemid bigint(20) to these tables
• Create a new FOREIGN KEY constraint from otherTable.postitemid to postitem(id)

Obviously, this may now have massive impact on the rest of your system.

One of the main benefits of simple surrogate keys (like AUTO_INCREMENT INTs) is that they are more resilient to change, which I guess we've just proven here.