ORA-02291 parent key not found, first

Question

My first programming program is an Oracle Database graduate certificate program, and the textbook is not Oracle friendly in some places (it is a generic database textbook). I had to rewrite the CREATE table commands and what I came up with is written below.

The tables are created in my database, and I can insert values into the vendor table; however, every time I insert values into the product table I receive the ORA-02291 integrity error.

I realize that the parent key is not being found in the vendor table, but I am at a loss as to why. I have tried a combination of column and table constraints on both tables, and nothing works. If someone could help me set up this relationship so I can practice that would be great!

 CREATE TABLE VENDOR(

 V_CODE     INTEGER         NOT NULL    CONSTRAINT VENDOR_P_K  PRIMARY KEY,
 V_NAME     VARCHAR(35)     NOT NULL,
 V_CONTACT  VARCHAR(25)     NOT NULL,
 V_AREACODE CHAR(3)         NOT NULL,
 V_PHONE    CHAR(8)         NOT NULL,
 V_STATE    CHAR(2)         NOT NULL,
 V_ORDER    CHAR(1)         NOT NULL
 );

----------------------------------------------------------------------------
CREATE TABLE PRODUCT(

 P_CODE     VARCHAR2(10)            CONSTRAINT PRODUCT_P_CODE_PK  PRIMARY KEY,
 P_DESCRIPT VARCHAR2(35)            NOT NULL,
 P_INDATE   DATE                    NOT NULL,
 P_QOH      NUMBER                  NOT NULL,
 P_MIN      NUMBER                  NOT NULL,
 P_PRICE    NUMBER(8,2)             NOT NULL,
 P_DISCOUNT NUMBER(5,2)             NOT NULL,
 V_CODE     INTEGER                 NOT NULL,
 CONSTRAINT V_CODE_FK               FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE)
 );

EDIT

INSERT INTO VENDOR 2
  VALUES (21225, 'Bryson, Inc.', 'Smithson', '615','223-3234','TN','Y');

INSERT INTO VENDOR 2
  VALUES (21226,'Superloo, Inc.','Flushing','904','215-8995','FL','N');

INSERT INTO PRODUCT 2 
   VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','03-Nov-13',8,5,109.99,0.00,25595);

Where is your insert statements?? Please show a [mcve] Please format your code properly. If you did not insert a record 1st into VENDOR, then you will get this error when trying to insert into product — OldProgrammer, Dec 24 '17 at 16:53
"I realize that the parent key is not being found in the vendor table, but I am at a loss as to why" - could you elaborate this statement?. — Kaushik Nayak, Dec 24 '17 at 17:01
@KaushikNayak ERROR at line 1: ORA-02291: integrity constraint (SYSTEM.V_CODE_FK) violated - parent key not found This is what I mean when the parent key is not being found. — ResilientMidget, Dec 24 '17 at 17:19
Please do not put important information about the question, such as your INSERT statements, into comments. Instead you should edit the question (there's a clickable `edit` button just above the tags) and add the information directly to the question. I've done it this time. Now - can you also please add the INSERT statement which caused the error? That would be the `INSERT INTO PRODUCT` statement. Thanks. — Bob Jarvis - Слава Україні, Dec 24 '17 at 17:29
Get in the habit of *always* including a field list with all INSERT statements. Not including a field list will get your code kicked out of every code review I've ever participated in, becaue when (not "if") a field is added to the VENDOR table your statement will immediately break. — Bob Jarvis - Слава Україні, Dec 24 '17 at 17:33
@BobJarvis thanks, like I said this is my first post I have no clue how this works yet. This is my second database class, and I am latching onto the concepts, so this will be a way of life for me hopefully. I could have posted the entire field list but I was hoping to save space, next time I will post everything. Thanks for the input everyone I appreciate it. — ResilientMidget, Dec 24 '17 at 17:41
By the way in Oracle there is [no good reason to use the CHAR datatype](https://stackoverflow.com/a/42165653/230471). I don't work for Oracle but I am pretty sure it is provided only for ANSI compatibility and portability reasons. — William Robertson, Dec 24 '17 at 18:06
The formatting is wonky with this textbook, and I have just been following it. The table structure and constraints did not work at all when I followed them in the beginning, so I had to use google to finally receive a TABLE CREATED. Thanks to whoever formatted my tables! — ResilientMidget, Dec 24 '17 at 18:23

Olivier Jacot-Descombes · Answer 1 · 2017-12-24T19:12:17.330

3

The relationship is set up correctly.

You can only insert foreign key values matching a primary key value in the master table. In your example, you are inserting a product with V_CODE = 25595 but you are never inserting a vendor with this V_CODE.

Maybe you intended to insert the vendor later. This does not work, as the database is enforcing the constraints for every command. Therefore, insert the vendor first and append his products later.

If you want to delete vendors, first delete its products, then delete the vendor unless you are using Foreign Keys with Cascade Delete.

edited Dec 24 '17 at 19:12

answered Dec 24 '17 at 17:59

Olivier Jacot-Descombes

104,806
13
138
188

1

It IS possible to arrange the constraints so that any-order insert can be carried out; defferred constraints are only checked upon transaction completion, so if the `SET CONSTRAINTS ALL DEFERRED` option were executed within the transaction, or if `ALTER SESSION SET ALL CONSTRAINTS DEFERRED` were executed to make all constraints act in deferred mode for the current session, then the inserts could be in any order and will only be verified upon transaction commit. Generally, I support the notion of executing inserts in the correct order, as there are few cases where it cannot be made to work – Caius Jard Dec 24 '17 at 18:55
@Olivier thank you for pointing that the V_CODE values do not match up. This crossed my mind last night, but I did not want to believe that the textbook instructions were wrong! I decided to plug V_CODE=25595 into the first INSERT INTO VENDOR command (instead of 21225), and my INSERT INTO PRODUCT command worked fine. For what its worth, the first three INSERT commands are what the textbook recommends we start off with after creating the product and vendor tables, and using mismatching V_CODEs was not a good idea. – ResilientMidget Dec 24 '17 at 19:37
I think I will start with all of the INSERTs on the one side of the relationship from now on, and then move on to the many side. – ResilientMidget Dec 24 '17 at 20:15

ORA-02291 parent key not found, first

EDIT

1 Answers1