Not able to insert value into table (Oracle)

Question

I keep getting Error report - ORA-01722: invalid number when I'm trying to insert values into my table.

I'm using oracle. The original problem was that the primary key for table store was a foreign key for table employee and vice versa, hence I could not insert any values. So I changed the foreign key in table store to NULL and tried that but still didn't work

create table Store(
Store_ID integer primary key,
Warehouse_ID integer not null,
Employee_ID integer,
Owner_name varchar2 (15) not null,
Store_hours varchar (10) not null,
Store_name varchar (20) not null,
Store_Address varchar2 (35) not null,
  CONSTRAINT Warehouse_FK_Store
  FOREIGN KEY (Warehouse_ID)
  REFERENCES Warehouse (Warehouse_ID),
  CONSTRAINT Employee_FK_Store
  FOREIGN KEY (Employee_ID)
  REFERENCES Employee (Employee_ID));

insert into Store
values (101, 1001, NULL , 'Grant Campbell', '7:00am - 10:00pm', 'Papakura', '331-345 Great South Road, Takanini, Auckland, 2110');

Answer 1

You have problems with the length of the fields. And you should include the columns being inserted.

I don't know what the types are for the referencing keys, but they need to be the same.

This works:

create table Stores (
    Store_ID integer primary key,
    Warehouse_ID integer not null,
    Employee_ID integer,
    Owner_name varchar2 (255) not null,
    Store_hours varchar2(255) not null,
    Store_name varchar2(255) not null,
    Store_Address varchar2(255) not null
    --  CONSTRAINT Warehouse_FK_Store  FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse (Warehouse_ID),
    --  CONSTRAINT Employee_FK_Store FOREIGN KEY (Employee_ID) REFERENCES Employee (Employee_ID)    
);

insert into Stores (Store_ID, Warehouse_ID, Employee_ID, Owner_name, Store_Hours, Store_name, Store_Address)
    values (101, 1001, NULL , 'Grant Campbell', '7:00am - 10:00pm', 'Papakura', '331-345 Great South Road, Takanini, Auckland, 2110');

See here.

Here is a version with the foreign key constraints.

Answer 2

Your query is right but your Store_Address varchar size is too short and in your query size is greater then i.e u need to change size of Store_Address varchar (35) to Store_Address varchar (255)

   create table Store(
    Store_ID integer primary key,
    Warehouse_ID integer not null,
    Employee_ID integer,
    Owner_name varchar2 (15) not null,
    Store_hours varchar (10) not null,
    Store_name varchar (20) not null,
    Store_Address varchar2 (255) not null,
      CONSTRAINT Warehouse_FK_Store
      FOREIGN KEY (Warehouse_ID)
      REFERENCES Warehouse (Warehouse_ID),
      CONSTRAINT Employee_FK_Store
      FOREIGN KEY (Employee_ID)
      REFERENCES Employee (Employee_ID));

    insert into Store
    values (101, 1001, NULL , 'Grant Campbell', '7:00am - 10:00pm', 'Papakura', '331-345 Great South Road, Takanini, Auckland, 2110');

Answer 3

Given value '331-345 Great South Road, Takanini, Auckland, 2110331-345 Great South Road, Takanini, Auckland, 2110' size is 100. Where corresponding created column 'Store_Address' size is 35.

It will work if you increase the size of the column 'Store_Address ' .