Regex for validating SQL

Question

I am writing a regex to validate that a name should be given when defining INDEX, KEY and UNIQUE while creating a new table. Some of the valid syntaxes are

KEY `id` (`id`),     
INDEX name (`name`),    
UNIQUE idx (`id`),     
UNIQUE KEY name (`name`),

Invalid syntaxes are

UNIQUE KEY (`name`),   
INDEX (`designation`),     
UNIQUE (`status`),

because there is no name provided while defining them.

I came up with a regex like this

(?i)(?:UNIQUE\s+KEY|UNIQUE\s+INDEX|KEY|INDEX|UNIQUE)\s*`?\w+`?\s*\(

But it matches

UNIQUE KEY (`name`),

also. Is there anyway I can improve the regex not to match the above string?

Try (?:KEY\s+`[^`]*`|(?:INDEX|UNIQUE\s+KEY)\s+\w+|UNIQUE\s+(?!KEY\s*\()\w+)\s*\(`[^`]*`\) — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 10, 2016 at 20:34
Sorry for the late reply. When I tried ur regex, I got error for unbalanced paranthesis. — prasanth
– prasanth, Commented Jun 13, 2016 at 18:28
Did you copy it from here? It must be a copy/paste issue. It works at the regex101.com. Using ` is tricky inside comments. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 13, 2016 at 18:33
Your "invalid" syntaxes are quite valid. I almost never explicitly name indexes. — Rick James
– Rick James, Commented Jun 20, 2016 at 6:31

LukStorms · Accepted Answer · 2016-06-11 18:58:56Z

1

You could add a negative lookahead after the first UNIQUE so that it would try the second OR and only then matches UNIQUE KEY.

(?i)(?:INDEX|UNIQUE(?! KEY)|UNIQUE KEY|KEY)\s+\W?[\w ]+\W?\s+\(\W?[\w ]+\W?\)

And the reverse, if you want to find the nameless which you pointed out as invalid:

(?i)(?:INDEX|UNIQUE(?! KEY)|UNIQUE KEY|KEY)\s+\(\W?[\w ]+\W?\)

answered Jun 11, 2016 at 18:58

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regex for validating SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related