Cannot get Canadian postal code to work and Province all caps

Question

I am trying to get my PL/SQL code to run but I keep running into issues. I am trying to get my Canadian postal code to be valid and it keeps giving getting error SQL Error: ORA-02290: check constraint (ORCL2_21.CK_INVESTOR_POSTAL_CODE) violated and the province needs to be two chars and all caps only can be valid and it wont compile the test code i have.

Create table Investor
(
  Investor_Number Number(7,0)
      Constraint PK_Investor_Investor_Number Primary Key
      Constraint NN_Investor_Investor_Number Not Null,
  First_Name Varchar2(25)
      Constraint NN_Investor_First_Name Not Null,
  Last_Name Varchar2(30)
      Constraint NN_Investor_Last_Name Not Null,    
  Street_Address Varchar2(35)
      Constraint NL_Investor_Street_Address Null,
  City Varchar2(25)
      Constraint NL_Investor_City Null,
  Province Char(2)
      Constraint CK_Investor_Province Check (Province in ('__'))
      Constraint CK_Investor_Province_Caps Check (Province = UPPER(province))
      Constraint NL_Investor_Province Null,
 Postal_Code Varchar2(7)
          Constraint CK_Investor_Postal_Code Check (REGEXP_like(Postal_Code,'[A-Z][0-9][A-Z][0-9][A-Z][0-9]')) --Having problem getting postal code to work on test inserts code!!!!!!
          Constraint NL_Investor_Postal_Code Null,
  Area_Code Number(3,0)
      Default '780'
      Constraint NN_Investor_Area_Code Not Null,
  Phone_Number Number(7,0) 
      Constraint NN_Investor_Phone_Number Not Null,
   Email_Address Varchar2(50)
      Constraint CK_Investor_Email_Address Check (REGEXP_LIKE (email_address, '^(\S+)\@(\S+)\.(\S+)$'))
      Constraint NN_Investor_Email_Address Not Null,
  Account_Number Number(7,0) Not Null,
      Constraint FK__Investor_Account_Number --Name of Constraint
      Foreign Key (Account_Number) -- Foreign Key Column name
      References Account(Account_Number)-- name of table and column trying to reference,
  );

Here is an insert statement which fails the constraint.

INSERT INTO Investor (Investor_Number, First_Name, Last_Name, Street_Address, City, Province, Postal_Code, Area_Code, Phone_Number, Email_Address, Account_Number) 
VALUES (1004, 'Tobias', 'Schneider', '10222 102 ST', 'Edmonton', 'AB', 'T5P 1W1', 780, 4997766, 'pellentesque@freenet.com', 7200);

It gives SQL Error:

ORA-02290: check constraint (ORCL2_21.CK_INVESTOR_POSTAL_CODE) violated

*"it keeps giving and error "* . Please tell us what error you get. Oracle has literally thousands of errors, runtime and compilation time. If you what to get a helpful answer you need to provide sufficient details. Obviously people will try to guess what problem you're having but really they shouldn't have to guess. — APC, Sep 15 '18 at 16:25
`Check (Province in ('__'))` will always be true for `char(2)` column. If you want to ensure it is two characters long, make it a normal `varchar2(2)` and check the length or specify a regex. — William Robertson, Sep 15 '18 at 16:47
@mohammedtarrabin - please **edit your question** to include clarifications or additional information. In particular please don't put code in comments as the lack of formatting and lost layout makes it too hard to read. — APC, Sep 16 '18 at 09:08

eaolson · Answer 1 · 2018-09-16T22:16:01.550

1

First, it's not clear what "getting it wrong" means. Are you getting an error or something? If so, it helps to show exactly what error you're getting.

Second, this works for me, in the sense that I can create the table, after removing the FK constraint on account_number, just because I don't have that table.

Third, You have several NULL constraints, but I assume those are supposed to be NOT NULL. Otherwise, they don't make sense.

Fourth, province should not be IN but probably LIKE.

I don't know what Canadian postal codes look like. Is A0A0A0 a valid postal code?

Personally, I'd get rid of the CHAR and use VARCHAR2 instead. Just never use CHAR. If your database is UTF-8, I'd also specify the columns with a length type, like VARCHAR2(25 CHAR).

Edit: Your postal code has a space in it, but your regex does not allow them. You might make it '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]', with a space between the third and fourth tokens..

edited Sep 16 '18 at 22:16

answered Sep 15 '18 at 03:19

eaolson

14,717
7
43
58

it needs to be null, and yes the canadian postal code is in that format.im getting error SQL Error: ORA-02290: check constraint (ORCL2_21.CK_INVESTOR_POSTAL_CODE) violated – mike Sep 15 '18 at 04:22
@mohammedtarrabain - that error just means your check constraint rejected a value. What was the value? Was it a valid postcode? – William Robertson Sep 15 '18 at 16:44
@WilliamRobertson INSERT INTO Investor (Investor_Number, First_Name, Last_Name, Street_Address, City, Province, Postal_Code, Area_Code, Phone_Number, Email_Address, Account_Number) VALUES (1004, 'Tobias', 'Schneider', '10222 102 ST', 'Edmonton', 'AB', 'T5P 1W1', 780, 4997766, 'pellentesque@freenet.com', 7200); – mike Sep 15 '18 at 21:30
I think you are saying `'T5P 1W1'` doesn't match regex `'[A-Z][0-9][A-Z][0-9][A-Z][0-9]'`, which as eaolson points out is because of the space. If this whole question boils down to a regex, you can check it on one of the regex tester sites like [regex101.com](https://regex101.com). – William Robertson Sep 16 '18 at 22:37

score 1 · Answer 2 · answered Sep 15 '18 at 10:04

Specifying NULL constraint is simply useless. Column will allow nulls by default, so - no point in doing that.

You said that you have problems with CK_INVESTOR_POSTAL_CODE; I don't:

SQL> create table investor
  2  ( Postal_Code Varchar2(7)
  3            Constraint CK_Investor_Postal_Code Check
  4            (REGEXP_like(Postal_Code,'[A-Z][0-9][A-Z][0-9][A-Z][0-9]'))
  5            --Having problem getting postal code to work on test inserts code!!!!!!
  6  );

Table created.

SQL> insert into investor values ('A1B2C3');

1 row created.

SQL> insert into investor values ('abcdef');
insert into investor values ('abcdef')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_INVESTOR_POSTAL_CODE) violated


SQL> insert into investor values ('123');
insert into investor values ('123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_INVESTOR_POSTAL_CODE) violated


SQL> insert into investor values ('AAABBB');
insert into investor values ('AAABBB')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_INVESTOR_POSTAL_CODE) violated


SQL>

Without providing what you did (i.e. what values you tried to insert into a table), it is difficult to guess what's wrong.

INSERT INTO Investor (Investor_Number, First_Name, Last_Name, Street_Address, City, Province, Postal_Code, Area_Code, Phone_Number, Email_Address, Account_Number) VALUES (1004, 'Tobias', 'Schneider', '10222 102 ST', 'Edmonton', 'AB', 'T5P 1W1', 780, 4997766, 'pellentesque@freenet.com', 7200); That is what i entered for values and it gave the error. — mike, Sep 15 '18 at 21:37
I'm away from the computer so I can't test it, but your postal code contains a space, while the constraint doesn't have one. — Littlefoot, Sep 15 '18 at 21:45

score 0 · Answer 3 · answered Sep 16 '18 at 09:13

Your sample postcode is 'T5P 1W1'. Your regex only enforces letters and digits, and doesn't allow for spaces. So either:

you need to change the postcode value so it is 'T5P1W1' or
you need to change the regular expression to include the space, e.g (REGEXP_like(Postal_Code,'[A-Z][0-9][A-Z] [0-9][A-Z][0-9]'))

Cannot get Canadian postal code to work and Province all caps

3 Answers3