-1

I have to create a database for my project and im currently struggling with creating custom checks. I want to create custom check on student personal identity number (pesel),I want it to be 11 characters long and to have only numbers from 0-9,also i want to create check on zip code to have this format :"11-111". I work in Oracle SQL developer.

These are the checks im trying to add but i have the same error in both of them.

ALTER TABLE Address ADD CHECK (Zip_Code ~ '^[0-9]{2}-[0-9]{3}');

ALTER TABLE Students ADD CHECK (pesel ~ '^[0-9]*$');

This is error im getting:

Error starting at line : 1 in command -

ALTER TABLE Adress ADD CHECK (Zip_Code ~ '^[0-9]{2}-[0-9]{3}')

Error report -

00911. 00000 -  "invalid character"

*Cause:  
  The identifier name started with an ASCII character other than a
           letter or a number. After the first character of the identifier
           name, ASCII characters are allowed including "$", "#" and "_".
           Identifiers enclosed in double quotation marks may contain any
           character other than a double quotation. Alternate quotation
           marks (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters. For all other contexts, consult the SQL Language
           Reference Manual.

*Action:  
 Check the Oracle identifier naming convention. If you are
           attempting to provide a password in the IDENTIFIED BY clause of
           a CREATE USER or ALTER USER statement, then it is recommended to
           always enclose the password in double quotation marks because
           characters other than the double quotation are then allowed.
Sassanek
  • 3
  • 1
  • 2
    [As documented in the manual](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Pattern-matching-Conditions.html#GUID-D2124F3A-C6E4-4CCA-A40E-2FFCABFD8E19) Oracle uses `regexp_like` not `~` - where in the manual did you find that `~` was used for regular expressions? –  Jan 13 '21 at 13:59

1 Answers1

1

If pattern is correct, you'd use regular expressions, e.g.

ALTER TABLE Address ADD CHECK (regexp_like(address, '^[0-9]{2}-[0-9]{3}'));
Littlefoot
  • 131,892
  • 15
  • 35
  • 57