Error report: SQL Error: A foreign key value has no matching primary key value. Completely baffled

Question

I can safely say that I am new to SQL and therefore upon writing the code to insert data into a table, I received an error report, that I can't seem to understand what it means, therefore I am hoping someone out there may be able to tell me what silly mistake I am making and remove a lot of stress ^.^

This is the error code I got:

Error report: SQL Error: ORA-02291: integrity constraint (H.VENDOR_ID_FK) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

Thanks in advance!

Solution

If I run all of that I get several errors before this point, which I'll skip over for now... the first one against this FK seems to be:

INSERT INTO parts_order VALUES
        (2
        ,2
        ,95115995
        ,'Delivered'
        ,'04/dec/2012'
        ,'01/jan/2013'
        ,'20/dec/2012'
        ,'Handler Pro'
        );

It's better to put the columns in the insert clause so you can see what lines up (i.e. INSERT INTO parts_order (order_id, job_id, vendor_id, ...) VALUES (4, 4, 95115995, ...), and also so you (and we) don't have to refer back to the table definition, and to avoid failures if the definition changes in the future. It's also better not to rely on implicit date conversions (i.e. use to_char('05/jan/2013', 'DD/mon/YYYY').

Anyway... the constraint it's complaining about is VENDOR_ID_FK, which we can see in the table definition:

CREATE TABLE parts_order
( order_id NUMBER(11)   
    CONSTRAINT order_id_pk  PRIMARY KEY
  ,job_id NUMBER(11)    
    CONSTRAINT job_id_fk REFERENCES maintenance(job_id)
  ,vendor_id    NUMBER(11)  
    CONSTRAINT vendor_id_fk REFERENCES parts_vendor(part_vendor_id)
  ,parts_status VARCHAR2(20)
  ,date_ordered DATE
  ,date_arrived DATE
  ,date_delivery_due    DATE
  ,part_name VARCHAR2(20) 
    CONSTRAINT part_name_nn NOT NULL);

... is against parts_vendor(part_vendor_id). What the error is saying is that the vendor_id you're inserting, 95115995, doesn't exist in the parent parts_vendor table - which is true, you only insert records with part_vendor_id values 1, 2, 3 and 4.

The constraint is working as intended - it's stopping you putting a 'child' record in without its 'parent' existing. You either need to create a parts_vendor record for ID 95115995, or change the vendor_id value you're trying to insert into parts_order to one that already exists.

Comments

[Tim]

Careful, you will earn the Blubberer badge.