1

I have a table

CREATE TABLE table_name
(
    EmpId VARCHAR(50),
    Name VARCHAR(100)
)

How can I restrict the EmpId column to consist of two letters followed by 3-5 digits? The following are all examples of valid values:

ac236, ak2356, av23695, ak365

I tried using the following check constraint:

ALTER TABLE table_name
ADD CONSTRAINT ck_table_name CHECK (EmpId NOT LIKE'%[^a-zA-Z0-9 ]%')

However, it allows all combinations of letters & digits, such as "23" and "fads":

INSERT INTO table_name
  VALUES
('23', 'Test 2'),
('fabs', 'Test 2');

inserted rows with invalid EmpIDs

If a value violates the format, I'd like the query to fail and print error message. For example, if 'na23' were inserted as the EmpID, MySQL could say:

Empid should be ab123/ab1234/a12345 format

Initially, I was using MySQL 5.7.11-0ubuntu6-log (which, it turns out, doesn't support CHECK constraints), but have upgraded to MySQL 8.0.17.

outis
  • 75,655
  • 22
  • 151
  • 221
joe
  • 73
  • 1
  • 8
  • What version of mysql are you on? – P.Salmon Jul 25 '19 at 07:47
  • 5.7.11-0ubuntu6-log – joe Jul 25 '19 at 08:37
  • 'Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:' - https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html . Either do this check in your front end or in a trigger. – P.Salmon Jul 25 '19 at 10:09

1 Answers1

1

Assuming it's MySQL >=8.0.16

check(EmpId regexp '^[a-z]{2}[0-9]{3,5}$')

James
  • 1,819
  • 2
  • 8
  • 21
  • numeric values are also inserting – joe Jul 25 '19 at 09:00
  • since you are using version less than 8.0.16 it won't work – James Jul 25 '19 at 09:16
  • ,i have upgraded to 8.0.17 its working, how to add error message for this please let me know like Empid should be ab123/ab1234/a12345 format – joe Jul 25 '19 at 10:41
  • It's quite a bit long, you need to use stored procedure to capture the error. There might be other solution too but you can't just do using only query. So where you need to show those error? If its in app side you can do it there. – James Jul 25 '19 at 10:49