Question 207918 in Mysql Question posted in
The official MySQL documentation can be found here.

Pattern constraint in mySql

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}$')
);
Tags:

2

Answers


  1. 0

    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 ~ '^20d{2}(CS|EC|MC|CV)d{3}(D|0)d{3}$')
);

    Fiddle Link

    Login or Signup to reply.
  2. 0 
    CREATE TABLE students (
  enroll_id VARCHAR(20) PRIMARY KEY,
  std_name VARCHAR(24),
  CONSTRAINT valid_enroll_id CHECK (enroll_id ~ '^20d{2}(CS|EC|MC|CV)d{3}[D0]d{3}$')
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search