MySQL check constraint violation

Question

I'm creating a MySQL database using the Oracle SQL Developer.

I have created a table for currencies where the Currency_Code column should contain a three character (alphabets only), capitalised currency code.

When I try to add a value it says that the constraint CHK_Currency_Code_1 has been violated, but I'm not sure why.

I'm quite new to SQL. Here is the script:

CREATE TABLE IF NOT EXISTS Currency (
    Currency_Code CHAR(3) NOT NULL UNIQUE,
    PRIMARY KEY ( Currency_Code ),
    CONSTRAINT CHK_Currency_Code_1 CHECK ( Currency_Code LIKE '[a-z][a-z][a-z]' ),
    CONSTRAINT CHK_Currency_Code_2 CHECK ( Currency_Code = UPPER( Currency_Code ) )
);

INSERT INTO Currency (Currency_Code)
VALUES ('USD');

Answer 1

Simply USE REGEXP instead of LIKE. You are using regular expression but with LIKE function, that was the error.

 CREATE TABLE IF NOT EXISTS Currency (
        Currency_Code CHAR(3) NOT NULL UNIQUE,
        PRIMARY KEY ( Currency_Code ),
        CONSTRAINT CHK_Currency_Code_1 CHECK ( Currency_Code REGEXP '[A-Z][A-Z][A-Z]' ),
        CONSTRAINT CHK_Currency_Code_2 CHECK ( Currency_Code = UPPER( Currency_Code ) )
    );
    
    INSERT INTO Currency (Currency_Code)
    VALUES ('USD');