I am a beginner in SQL and I want to do something on my table 'User'.

I would like to make a constraint on my 'Password' column. The constraint is "Passwords only can be made of letters and digits, the only symbol allowed is "_" "

I don't know how to allowed only one symbol with a check() constraint.

I searched a lot on google and didn't find the solution.

I was thinking something like this :

CONSTRAINT TABLE_PASSWORD check ( password ........ )

CodePudding user response：

As mentioned in the comments, real applications should not store raw passwords in the DB. But since you have clarified this is for a school project, I hope this helps you:

As you said, you can use a CHECK constraint. You will likely want to use it in combination with REGEXP or NOT REGEXP.

Probably something like:

ALTER TABLE mytable
ADD CONSTRAINT myconstraint CHECK (mycolumn NOT REGEXP '[^A-Z0-9]')

You can see it working in this Fiddle.

CodePudding user response：

A simple REGEXP would be enough. Below query only allows digits , letters and _ symbol:

CREATE TABLE tablename (
    id int ,
    `password` varchar(25) CONSTRAINT TABLE_PASSWORD  CHECK (`password` REGEXP '^[a-zA-Z0-9 _]*$') );

https://dbfiddle.uk/X05evHMm

Note. Use plain text passwords only for testing purposes