How can I create a REGEXP constraint on a column?

How can I create a REGEXP constraint on a column?

Problem Description:

I am trying to put a regexp constraint on a field "StaffAdress" such that it should only end with " street".

This is my attempt, but it still doesn’t seem to be valid. I tried regexp ‘.*street$’ and regexp ‘street$’ but both did not let me add "test street" in the table.

CREATE TABLE MsStaff (
   `StaffId` CHAR(4) PRIMARY KEY CHECK (`StaffId` regexp 'S[0-9][0-9][0-9]'),
    `StaffName` VARCHAR(100) NOT NULL,
    `StaffGender` VARCHAR(6) NOT NULL,
    `StaffAddress` VARCHAR(100) NOT NULL CHECK ('StaffAddress' regexp '.*street$')
)

I’m using 10.4.19-MariaDB

Solution – 1

I think your problem is that you are comparing a literal string, not a column, to your regex.

...CHECK ('StaffAddress' regexp '.*street$')

should be (don’t forget the space)

...CHECK (StaffAddress regexp '.* street$')

Solution – 2

'StaffAddress' regexp '.*street$' will always return false. Column names have to be quoted in backticks, not in single quotes.

Correct would be:

`StaffAddress` VARCHAR(100) NOT NULL CHECK (`StaffAddress` regexp 'w* street$')
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject