CODEWITHSUNDEEP
regexawksed
S.Dan
S.Dan

Reputation: 1912

Regex to find (and replace) a foreign key in SQL script

I have an sql script with table creation scripts for multiple tables. I need a script (sed) to update a foreign key reference in one of them.

Is it possible to detect the FOREIGN KEY (MY_KEY_ID) REFERENCES fff(ID) ON DELETE CASCADE , from the following script.

Note that it might not be the only occurrence of that particular text, therefore I need to find the one that occurs after CREATE TABLE MYTABLE.

CREATE TABLE MYTABLE (
  blah
  .....
  FOREIGN KEY (MY_KEY_ID) REFERENCES fff(ID) ON DELETE CASCADE ,
  FOREIGN KEY ....
)

UPDATE:

The end goal is to use a bash script to remove ON DELETE CASCADE from one key and add it to the next key.

Upvotes: 0

Views: 150

Answers (1)

Ed Morton
Ed Morton

Reputation: 203655

This will find the FOREIGN KEY string after the CREATE TABLE string but now what?

$ awk '
    index($0,"CREATE TABLE MYTABLE") { f=1 }
    f && index($0,"FOREIGN KEY (MY_KEY_ID) REFERENCES fff(ID) ON DELETE CASCADE ,")
' file
FOREIGN KEY (MY_KEY_ID) REFERENCES fff(ID) ON DELETE CASCADE ,

Upvotes: 1

Related Questions