How to write a check constraint to verify if login is correct or not?

October 27, 2022

I searched the internet for 2 days to find the answer and couldn’t.

Basically I have this as a problem : "The login is composed of the first letter of the first name and the first 7 letters of the name (in lowercase) followed by two numbers."

My problem relies in the last 4 words "followed by two numbers". The first part I have done it correctly and it validates it, but i cant seem to find the answer to the problem specified above.

I tried doing something like this.

    CONSTRAINT ck_user_login CHECK
        (login LIKE SUBSTR(prenom,1, 1) || LOWER(SUBSTR(nom, 0, 7)) || '%[0-9]%')

But that does not seem to work.

All help will be appreciated. Thank you in advance.

>Solution :

I’d do it in two steps: first check first and last name, then check digits. Something like this:

SQL> CREATE TABLE test
  2  (
  3     prenom   VARCHAR2 (10),
  4     nom      VARCHAR2 (10),
  5     login    VARCHAR2 (20)
  6  );

Table created.

SQL> ALTER TABLE test
  2     ADD CONSTRAINT ck_user_login CHECK
  3            (    SUBSTR (login, 1, LENGTH (login) - 2) LIKE
  4                    SUBSTR (prenom, 1, 1) || LOWER (SUBSTR (nom, 1, 7))
  5             AND REGEXP_LIKE (login, '\d{2}$'));

Table altered.

Testing:

SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23');

1 row created.

SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfootacd23');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfootacd23')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated


SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot235');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot235')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated


SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23x');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23x')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated


SQL>