0

I want to create a check constraint for state abbreviations. I only want to allow a two character insert for the State column. What would be the code? my code was: State VARCHAR(2) NOT NULL CONSTRAINT CK_StateAbbr CHECK (State like 'xx')

I am assuming putting the xx in there will only allow xx for the state. Would I put CHECK (State like '[A-Z][A-Z]')?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Michael Ganschow
  • 3
  • 4
  • Only on MySQL 8.0 or MariaDB otherwise it will be ignored. – Lukasz Szozda Dec 01 '17 at 21:32
  • Your regex will work (assuming Mysql 8.0+ otherwise you'll need to use a trigger [like in this example](https://stackoverflow.com/questions/16005283/is-it-possible-to-enforce-data-checking-in-mysql-using-regular-expression)). You could also use `CHECK(State like '[A-Z]{2}')` or `CHECK(State like '[a-zA-Z]{2}')` if you want to allow lower case. – JNevill Dec 01 '17 at 21:33
  • Why not have a table with state codes and just use a foreign key to that table? – Joel Coehoorn Dec 01 '17 at 21:38

1 Answers1

1

With most versions of MySQL, it makes no difference. A check constraint is accepted as syntax, but it is not implemented.

That leaves you with several choices. First, ignore the issue. This would be the most common approach. Second, use a trigger. That makes for messy code and is not optimal.

The third approach is to create a table with all valid state abbreviations. Then you can use a foreign key constraint, to ensure that the value is valid.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786