How can I create a REGEXP constraint on a column?
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$')