CODEWITHSUNDEEP
mysql
eliesmith
eliesmith

Reputation: 59

MYSQL getting error while using check for Date of birth

I am a very beginner in MySQL and I am trying to create my first table with MySQL with CHECK and I am having trouble. I did bunch of research but was not able to find anything. What I am trying to do is:

  1. EmployeeId should be in format of E### (but when I enter E1234, the table is accepting the value) In reality it should not). It should only accept the value that starts with e and has only has 3 numbers after E.

  2. The main problem I am facing is that to CHECK DOB. What I have to do is that I have to not accept the value of DOB that is less than 16 years and the has to enter the full date not the age. And I am having trouble in that. Any help would be appreciated. Here is code. Thanks in advance.

     CREATE TABLE Employee (
 EmployeeID varchar(200) NOT NULL PRIMARY KEY
    CHECK (EmployeeID REGEXP '^E[0-9]+$'),
 DOB date CHECK (DOB >= CURRENT_DATE - INTERVAL 16 YEAR),
 FirstName varchar(200),
 MiddleName varchar(200),
 LastName varchar(200),
 Address varchar(255),
 StartDate date,
 Gender varchar(100)
 );`

And the error I am getting is Error Code: 3814. An expression of a check constraint 'employee_chk_2' contains disallowed function: curdate.

Upvotes: 0

Views: 311

Answers (2)

nbk
nbk

Reputation: 49375

As already said, Current_date is a non deterministic function and as such can't be used.

One possibility is to write a trigger before insert, and you need one for BEFORE UPDATE also

DELIMITER $$
CREATE TRIGGER before_Employee_insert
BEFORE INSERT
ON Employee FOR EACH ROW
BEGIN

    IF NEW.DOB >= CURRENT_DATE - INTERVAL 16 YEAR THEN
        SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'employee is too young';
    END IF; 

END$$
DELIMITER ;

CREATE TABLE Employee (
 EmployeeID binary(4) NOT NULL PRIMARY KEY
    CHECK (EmployeeID REGEXP '^E[0-9]{3}$'),
 DOB date ,
 FirstName varchar(200),
 MiddleName varchar(200),
 LastName varchar(200),
 Address varchar(255),
 StartDate date,
 Gender varchar(100)
 );`

CREATE TRIGGER before_Employee_insert
BEFORE INSERT
ON Employee FOR EACH ROW
BEGIN

    IF NEW.DOB >= CURRENT_DATE - INTERVAL 16 YEAR THEN
        SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'employee is too young';
    END IF; 

END

INSERT INTO Employee VALUES('E001','2006-04-25','test','test','test','myqadress', Current_Date(),'male')

employee is too young

db<>fiddle here

Upvotes: 1

danblack
danblack

Reputation: 14666

CHECK only accepts deterministic built-in functions, which CURRENT_DATE definitely isn't.

If EmployeeID only accepts 3 characters after E then the regex should be '^E[0-9]{3}$ and the column should be binary(4).

Upvotes: 1

Related Questions