phpmyadmin Question posted in
The official documentation can be found here.

Trying to add check constraint for password column but keep getting these errors – Phpmyadmin

I want to add a constraint for the password column to ensure each password entered is atleast 6 characters but keep getting these errors CHECK errors

Tags:

2

Answers


  1. 0

    ALTER TABLE documentation shows the syntax requires a contraint name:

    ALTER TABLE customers
ADD CONSTRAINT passwordlength
  CHECK(char_length(Passwords) >= 6)

    Recommend not even playing with clear text passwords.If its worth learning its worth learning to do it correctly.

    Login or Signup to reply.
  2. 0

    You set the tag to MySQL, so here is the correct syntax for MySQL server 8:

    ALTER TABLE customers
    ADD CONSTRAINT CHECK (LENGTH(Passwords) >= 6) ENFORCED;

    This won’t work on MariaDB 10, because the ENFORCED is not supported. In other words, it is useless.

    That said, the only alternative is to use a trigger:

    CREATE TRIGGER t1
    BEFORE INSERT
    ON customers
    FOR EACH ROW SET NEW.Passwords = IF(
        LENGTH(NEW.Passwords) < 6,
        NULL,
        NEW.Passwords);

    This causes all new records to have a NULL password if they were too short. You would need another trigger for the UPDATE.

    Of course, this all is insecure. Do not store plain-text passwords. You will not get hired / promoted. Only password hashes should be stored instead. These hashes usually have a fixed length, independent of the password’s length. The hashes are usually not generated / checked by the database, but in PHP. The length check thus usually doesn’t happen at the database level, but has to be moved to the application.

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

Back To Top
Search