CODEWITHSUNDEEP
sqlconstraints
user3716005
user3716005

Reputation: 85

SQL Constraint of the first letter being upper case

ALTER TABLE info
ADD CONSTRAINT uppercase 
CHECK (password = UPPER(substr(password, 1 ,1 )));

I am curious to why my constraint doesnt work? First time trying to write a constraint, so bear with me here.

Upvotes: 2

Views: 4064

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you are using MySQL, it doesn't work because constraints are not enforced. In some other databases, the default collation for = has upper and lower case being the same. And, whatever the default collation, it might be changed for your database.

One way you can readily do this:

ALTER TABLE info
    ADD CONSTRAINT uppercase CHECK (ASCII(LEFT(password, 1)) BETWEEN ASCII('A') and ASCII('Z'));

That said, in most cases, passwords should not be stored in an unencrypted way in the database. This is very dangerous. You can encrypt the password at the database layer. I think it is even better to do encryption at the client layer, so the the free-text password doesn't even make it on the network.

Upvotes: 5

Related Questions