{"id":4364,"date":"2014-04-01T20:57:40","date_gmt":"2014-04-02T00:57:40","guid":{"rendered":"http:\/\/www.dr-chuck.com\/csev-blog\/?p=4364"},"modified":"2014-04-01T20:57:40","modified_gmt":"2014-04-02T00:57:40","slug":"altering-a-unique-constraint-in-a-mysql-table","status":"publish","type":"post","link":"https:\/\/www.dr-chuck.com\/csev-blog\/2014\/04\/altering-a-unique-constraint-in-a-mysql-table\/","title":{"rendered":"Altering a UNIQUE Constraint in a MySQL Table"},"content":{"rendered":"<p>It took me a while to figure out how to drop and recreate a UNIQUE constraint on one of my tables.  So I figured I would record the slick little sequence of commands here to help my memory and save me time next time:<\/p>\n<p><code>SHOW CREATE TABLE t_lti_link;<br \/>\n&nbsp;<br \/>\nCREATE TABLE `t_lti_link` (<br \/>\n  `link_id` int(11) NOT NULL AUTO_INCREMENT,<br \/>\n  `link_sha256` char(64) NOT NULL,<br \/>\n  `link_key` varchar(4096) NOT NULL,<br \/>\n  `context_id` int(11) NOT NULL,<br \/>\n  `title` varchar(2048) DEFAULT NULL,<br \/>\n  `json` text,<br \/>\n  `created_at` datetime NOT NULL,<br \/>\n  `updated_at` datetime NOT NULL,<br \/>\n  PRIMARY KEY (`link_id`),<br \/>\n  UNIQUE KEY `<strong>link_sha256<\/strong>` (`link_sha256`),<br \/>\n  KEY `t_lti_link_ibfk_1` (`context_id`),<br \/>\n  CONSTRAINT `t_lti_link_ibfk_1` FOREIGN KEY (`context_id`) REFERENCES `t_lti_context` (`context_id`) ON DELETE CASCADE ON UPDATE CASCADE<br \/>\n) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8<br \/>\n&nbsp;<br \/>\nALTER TABLE t_lti_link DROP INDEX <code>link_sha256<\/code>;<br \/>\n&nbsp;<br \/>\nSHOW INDEX FROM t_lti_link;<br \/>\n&nbsp;<br \/>\nALTER TABLE t_lti_link ADD UNIQUE(link_sha256, context_id)<br \/>\n&nbsp;<br \/>\nSHOW INDEX FROM t_lti_link;<br \/>\n&nbsp;<br \/>\nSHOW CREATE TABLE t_lti_link;<br \/>\n&nbsp;<br \/>\nCREATE TABLE `t_lti_link` (<br \/>\n  `link_id` int(11) NOT NULL AUTO_INCREMENT,<br \/>\n  `link_sha256` char(64) NOT NULL,<br \/>\n  `link_key` varchar(4096) NOT NULL,<br \/>\n  `context_id` int(11) NOT NULL,<br \/>\n  `title` varchar(2048) DEFAULT NULL,<br \/>\n  `json` text,<br \/>\n  `created_at` datetime NOT NULL,<br \/>\n  `updated_at` datetime NOT NULL,<br \/>\n  PRIMARY KEY (`link_id`),<br \/>\n  <strong>UNIQUE KEY `link_sha256` (`link_sha256`,`context_id`),<\/strong><br \/>\n  KEY `t_lti_link_ibfk_1` (`context_id`),<br \/>\n  CONSTRAINT `t_lti_link_ibfk_1` FOREIGN KEY (`context_id`) REFERENCES `t_lti_context` (`context_id`) ON DELETE CASCADE ON UPDATE CASCADE<br \/>\n) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8<br \/>\n<\/code><\/p>\n<p>Sweet.   I am loving that MySQL magic.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It took me a while to figure out how to drop and recreate a UNIQUE constraint on one of my tables. So I figured I would record the slick little sequence of commands here to help my memory and save me time next time: SHOW CREATE TABLE t_lti_link; &nbsp; CREATE TABLE `t_lti_link` ( `link_id` int(11) [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-4364","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/4364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/comments?post=4364"}],"version-history":[{"count":7,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/4364\/revisions"}],"predecessor-version":[{"id":4371,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/4364\/revisions\/4371"}],"wp:attachment":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/media?parent=4364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/categories?post=4364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/tags?post=4364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}