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

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.

CodePudding user response：

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>

CodePudding user response：

Two digits would be '[0-9][0-9]', not '%[0-9]%'; however, the LIKE operstor does understand this. The best you can do with LIKE is to test whether there are two characters after the name

CONSTRAINT ck_user_login CHECK
   (login LIKE SUBSTR(prenom, 1, 1) || LOWER(SUBSTR(nom, 1, 7)) || '__')

Since the the login is of fixed length, there is no need to add the wildcard % which specifies that any characters can appear multiple times.

You must use REGEXP_LIKE for more complex patterns

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

I suggest not to compare the whole login with REGEXP, since then you would have to escape the name part if any characters conflict with special REGEXP characters.

Other SQL dialects allow for more complex LIKE operators like Like P[A-F]### in Access for instance or character ranges LIKE '[0-9]' in Transact-SQL.