3

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?

user755806
  • 6,565
  • 27
  • 106
  • 153

1 Answers1

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.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • I tried. But it gave an error: Error Code : 1553 Cannot drop index 'UK_4cscn0qw8u7go5hvmofq0ersg': needed in a foreign key constraint – user755806 Apr 25 '15 at 07:10
  • you said "Add the new createdBy_id column (same type)". In which table do i need to add? – user755806 Apr 25 '15 at 07:47
  • @user755806 in all tables referencing this constraint. This [query will help](http://stackoverflow.com/a/1133461/314291) - its the `TABLE_NAME` output column is the one you need. – StuartLC Apr 25 '15 at 07:53
  • Thanks much for your answer. But I dont understand why createdBy_id is required in referencing tables? – user755806 Apr 25 '15 at 07:57
  • You've inherited a bit of a messy design - mostly, you'll want all referencing tables to use a tables primary key on a foreign key constraint. However, in this case, someone has decided to add a foreign key to `postitem(post_category_id, item_name)` which is preventing you from changing the constraint. – StuartLC Apr 25 '15 at 08:07