Check constraint for current date and expire date on credit card-CodePudding

Currently learning MariaDB, struggling to add a check constraint for a payments table. I want to check that the inputted expiration date for the credit/debit card is after the current date.

Create table payments (
customerID          int(11)         Not Null,
debit_credit_Num    varchar(20)     Not Null,
expireDate          date            Not Null,
CVS                 int(3)          Not Null,
billaddress         varchar(100)    Not Null,
billcity            varchar(100)    Not Null,
billprovince        varchar(10)     Not Null,
billpostalcode      varchar(10)     Not Null,
billcountry         varchar(100)    Not Null,

Constraint fk_has_customerpayment Foreign Key (customerID) References customers(customerID),

Constraint chk_expdate check (date('now') < expireDate);
)

2 errors were found during analysis.

A symbol name was expected! A reserved keyword can not be used as a column name without backquotes. (near "check" at position 463).
Unrecognized statement type. (near "date" at position 470)

MariaDB said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 14

Input query sample

Insert into payments (customerID, debit_credit_Num, expireDate, cvs, billAddress, billcity, billprovince, billpostalcode, billcountry) values

(1, '1234 5678 9123 4567', '2025-07-01', 213, '123 Church St', 'Burnaby', 'BC', 'V5A 1S6', 'Canada'),

(2, '0968 1723 2859 1902', '2025-04-01', 874, '321 W Popeyes Dr', 'Vancouver', 'BC', 'V6B 2J2', 'Canada')

(2, '0968 1723 2859 1111', '1999-04-01', 874, '321 W Popeyes Dr', 'Vancouver', 'BC', 'V6B 2J2', 'Canada'); <--- Should fail constraint

Edit 1 Thank you Umut TEKİN, I tried making a trigger like so but I don't know where I went wrong

CREATE TRIGGER chk_expdate
BEFORE INSERT
ON payments 
FOR EACH ROW
BEGIN
IF (new.expiredate < CURRENT_DATE) THEN
    SIGNAL SQLSTATE '45000' 
           SET MESSAGE_TEXT = 'Card has expired'; [<------ Line 8]
END IF;
END;

Error Message: MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 8

It works on https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=efb986c7edb6b4a3755639e2a380cae4 but not on PHPMyAdmin.

Edit 2 Managed to make trigger work by adding a delimiter on phpmyadmin following Paul T's answer to link

delimiter//
CREATE TRIGGER chk_expdate 
BEFORE INSERT ON payments 
FOR EACH ROW BEGIN 
IF (new.expiredate < CURRENT_DATE) THEN 
SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = 'Card has expired'; 
END IF; 
END;

CodePudding user response：

You have 2 problem here. First one syntax (missing parenthesis or misplaced semicolon). It should be like this:

Create table payments (
customerID          int(11)         Not Null,
debit_credit_Num    varchar(20)     Not Null,
expireDate          date            Not Null,
CVS                 int(3)          Not Null,
billaddress         varchar(100)    Not Null,
billcity            varchar(100)    Not Null,
billprovince        varchar(10)     Not Null,
billpostalcode      varchar(10)     Not Null,
billcountry         varchar(100)    Not Null,

Constraint fk_has_customerpayment Foreign Key (customerID) References customers(customerID),

Constraint chk_expdate check (check condition));

Second one is date(now()) or curdate() are nondeterministic functions. So this usage is not allowed.

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

Reference

Yet, triggers allow you to do that. It' s better also to check here.