CODEWITHSUNDEEP
sqloracle-databaseforeign-keyssql-insert
lnjblue
lnjblue

Reputation: 150

Foreign key insertion error SQL Developer

I have tried applying the foreign key in many ways, but I am getting the same error when inserting a row to my table which is:

INSERT INTO MEDICINE 
VALUES (50, 'PREDNISONE', '8 BOTTLES', 'N', 
        TO_DATE('12-MAR-2019','DD-MON-YYYY'), 
        TO_DATE('09-JAN-2016','DD-MON-YYYY'), 2);

I have created my table in many ways:

CREATE TABLE MEDICINE
(
    M_ID NUMBER PRIMARY KEY,
    M_NAME VARCHAR2(50),
    QUANTITY NUMBER,
    OTC VARCHAR2(1),
    EXPR DATE,
    LAST_AUDIT DATE,
    PT_ID NUMBER,

    CONSTRAINT fk_MED FOREIGN KEY (PT_ID)
    REFERENCES PHARM_TECH(PT_ID)
);

I've also tried:

CREATE TABLE MEDICINE
(
    M_ID NUMBER PRIMARY KEY,
    M_NAME VARCHAR2(50),
    QUANTITY NUMBER,
    OTC VARCHAR2(1),
    EXPR DATE,
    LAST_AUDIT DATE,
    PT_ID NUMBER
);

ALTER TABLE MEDICINE
ADD FOREIGN KEY (PT_ID)
REFERENCES PHARM_TECH(PT_ID);

I've also attempted:

CREATE TABLE MEDICINE
(
     M_ID NUMBER PRIMARY KEY,
     M_NAME VARCHAR2(50),
     QUANTITY NUMBER,
     OTC VARCHAR2(1),
     EXPR DATE,
     LAST_AUDIT DATE,
     PT_ID NUMBER REFERENCES PHARM_TECH(PT_ID)
);

These all run fine and creates the table without issues, however, I keep receiving this error when inserting the row above:

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

What's causing this?

Upvotes: 0

Views: 94

Answers (1)

Vance
Vance

Reputation: 897

You cannot insert the value '8 BOTTLES' into column QUANTITY. The data type is Number not varchar. try 8 or try changing the datatype of column Quantity to varchar2

Upvotes: 1

Related Questions