Pattern constraint in mySql

Question

I want to create a table for student with the attributes enroll_id and std_name. The enroll_id is Primary Key of the table and the format of enroll_id is year+brach code (CS,EC,MC,CV)+college code (001 to 300)+ entry type(D for lateral entry or 0 for not) + 3 digit number example 2021CS213D011 , 2021CV2010011.

CREATE TABLE students (
  enroll_id VARCHAR(20) PRIMARY KEY,
  std_name VARCHAR(24),
  CONSTRAINT valid_enroll_id CHECK (enroll_id ~ '^\d{4}(CS|EC|MC|CV)\d{3}(D|0)\d{5}$')
);

The `~` is postgres syntax... or as far as I know. What mysql version are you using? — user3783243, Jul 14 '23 at 15:18
Are you asking how to convert a PostgreSQL script into MySQL? The [Regular Expressions docs](https://dev.mysql.com/doc/refman/8.0/en/regexp.html) show that to check for a match you use the `REGEXP` or `RLIKE` operators, not `~` — Panagiotis Kanavos, Jul 14 '23 at 15:21
In fact, there are duplicate questions like this, much clearer one: [Is it Possible to Enforce Data Checking in MySQL using Regular expression](https://stackoverflow.com/questions/16005283/is-it-possible-to-enforce-data-checking-in-mysql-using-regular-expression) — Panagiotis Kanavos, Jul 14 '23 at 15:22

score 0 · Accepted Answer · answered Jul 14 '23 at 15:41

For MySQL, try the following to define your table -

CREATE TABLE students (
  enroll_id VARCHAR(20) PRIMARY KEY,
  std_name VARCHAR(24),
  CONSTRAINT valid_enroll_id CHECK (enroll_id REGEXP '^20[0-9]{2}(CS|EC|MC|CV)[0-9]{3}(D|0)[0-9]{3}$')
);

Fiddle Link

For Postgres, use the following to define your table -

CREATE TABLE students (
  enroll_id VARCHAR(20) PRIMARY KEY,
  std_name VARCHAR(24),
  CONSTRAINT valid_enroll_id CHECK (enroll_id ~ '^20\d{2}(CS|EC|MC|CV)\d{3}(D|0)\d{3}$')
);

Fiddle Link

Pattern constraint in mySql

1 Answers1