Removing a line, plus drop the comma of the last line with a matching pattern

Question

DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`),
  CONSTRAINT `FK6661B19126D878D` FOREIGN KEY (`answer_id`) REFERENCES `ans` (`id`),
  CONSTRAINT `FK6661B1975B33071` FOREIGN KEY (`id`) REFERENCES `apobj` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

My goal is to drop all constraints from a database, so I would like to have simple code search for the word "CONSTRAINT" and drop the line

I tried to use sed

sed '/\s*CONSTRAINT/d' ~/Downloads/dump.sql > ~/ouput.sql

but there are all these tailing comma that are left behind because of CONSTRAINTS being the last statement. I don't mind if it awk, sed, or some common tools.

The desired output is

DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Answer 1

sed is an excellent tool for simple substitutions on a single line but for anything else just use awk. Here's GNU awk for multi-char RS and the \s abbreviation for [[:space:]]:

$ awk -v RS='^$' -v ORS= '{gsub(/,\s*CONSTRAINT[^\n,]+/,"")}1' file
DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

For a POSIX-only solution (see comments below from @mklement0):

awk -v RS=$(printf '\3') -v ORS= '{gsub(/,[[:space:]]*CONSTRAINT[^\n,]+/,"")}1'