CODEWITHSUNDEEP
sqloracle-database
mike
mike

Reputation: 27

Keep getting error ORA-02438 when trying to create table

I'm trying to get my code to run but it keeps giving me this error. I'm new to SQL and haven't gotten used to it yet and I keep trying to fix it but I can't seem to get it to work. I've put both tables into the description code below. 

 Create table Account
(
    Account_Number Number(7,0)
      Constraint PK_Account_Account_Number Primary Key
      Constraint NN_Account_Account_Number Not Null,  
    Account_Balance Number(13,2)
      Constraint NN_Account_Account_Balance Not Null
);


  Create table Investor
(
  Investor_Number Number(7,0)
      Constraint PK_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 NL_Investor_Province Null,
  Postal_Code Varchar2(7)
      Constraint CK_Investor_Postal_Code Check (REGEXP_like(string,'[A-Z]-[0-9]-[A-Z]-[0-9]-[A-Z]-[0-9]'))
      Constraint NL_Investor_Posta_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,
  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,
  );

Upvotes: 1

Views: 95

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

The check constraint in Postal_Code is referring to string instead of the actual column name; it should be:

  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]'))
      Constraint NL_Investor_Postal_Code Null,

db<>fiddle

The error you are getting is because the parser is interpreting string as an identifier, and assuming it's the name of another column, even though it isn't. There isn't anything else it could obviously be though,so it's not entirely unreasonable.

Upvotes: 1

Related Questions