Stream-based GNU sed solution:
#Unless on the last line, read the next line and append it to the pattern space
$!N
#If the current pair of lines in buffer, matches the "/,\nPRIMARY KEY/" pattern
/,\n\?\s*PRIMARY KEY/ {
#Read the following lines, until "/) ENGINE/" pattern is encountered
:loop
/) ENGINE/ b exit
N
b loop
}
#Strip away everything between ", PRIMARY KEY" and ") ENGINE"
:exit
s/,\n\?\s*PRIMARY KEY.*\() ENGINE\)/\n\1/
#Print the content of the pattern space up to the first newline (i.e. the first line out of two)
P
#Delete everything up to the first newline (leaving the second line in pattern space buffer)
#and restart the cycle
D
Run as follows:
cat data.txt|sed -nf script.sed
(you can compress this to one-liner, by removing comments and replacing newlines with ";").
Version by @Philippos:
With some simplification and more portable:
sed -e '$!N;/,\n *PRIMARY KEY/!{P;D;};s/,//;:loop' -e 'N;s/ *PRIMARY KEY.*\() ENGINE\)/\1/;T loop'