Is it possible to create DDL table where specific attribute has to end with digit?

Question

I'm new to MySQL. I'm trying to create table "Customer" with attribute "Password".

I'd like to know is it possible in DDL to make a constraint,that password has to contain 5 chars, while the last one is digit(one of this: 0-9)

I have tried to search for the answer, but could not find one. I'm pretty sure that I cant satisfy this condition, but I will be glad to hear an oponion of someone who understands better then me. Thank you!

Tim Biegeleisen · Accepted Answer · 2019-12-13T13:33:14.653

1

Your requirement should never be an actual consideration, because you should not be storing clear text passwords in your MySQL database in the first place. Instead, you should be checking password creation in your PHP server code (as well as possibly on the front end). If valid, you should be hashing your passwords irreversibly, and then storing the hash in the user table. Your exact specified requirements can be gotten using the following regex pattern:

^.{4,}\d$

This would match 5 or more characters of any kind, the last of which is a digit. For some more ideas on a better password strength, and how to write a regex for that, consider reading the canonical SO question Regex to validate password strength.

Edit:

Given that it appears you are using SQL Server, if you really needed a clear text password column with your requirements, you could use a check constraint:

CREATE TABLE users (
    id INT NOT NULL,
    password VARCHAR(100) NOT NULL,
    CONSTRAINT check_password
        CHECK (LEN(password) >= 5 AND RIGHT(password, 1) LIKE '[0-9]')
);

edited Dec 13 '19 at 13:33

answered Dec 13 '19 at 07:27

Tim Biegeleisen

502,043
27
286
360

Thank you for the answer. I understand that storing a password its not something that I should do. But it is a college project and this is what I was asked for. Hence, I do have to know if this constraint is possible while creating new table. – sasha Dec 13 '19 at 07:38
If you are using MySQL 8+, then it is possible to enforce this using a check constraint. On earlier versions of MySQL, you would have to write a before insert trigger to handle this. – Tim Biegeleisen Dec 13 '19 at 07:43
I'm using phpstorm – sasha Dec 13 '19 at 07:44
That's not a MySQL version, that is a PHP framework name. – Tim Biegeleisen Dec 13 '19 at 07:44
Microsoft SQL Server (ver. 12.00.2000) Sorry, this is what I have. – sasha Dec 13 '19 at 13:07
@AlexD I have updated my answer with an option for SQL Server. – Tim Biegeleisen Dec 13 '19 at 13:33

score 0 · Answer 2 · answered Dec 13 '19 at 07:24

0

Why not make a PHP page for the entries and place all restrictions on that page?

As in MySql, you can't have so many restrictions.

You can also sue MySql procedure for inserting values and place restrictions from there.

answered Dec 13 '19 at 07:24

Ankit Sharma

3,923
2
29
49

Hello and thank you for the answer. What I'm doing is a college project, so the demands here are not of the "real world" All I need to know is it possible to make this (or similar) constraint while creating new table – sasha Dec 13 '19 at 07:43

Is it possible to create DDL table where specific attribute has to end with digit?

2 Answers2