Question 112870 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Constraint SQL on column password

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 …….. )

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search