CODEWITHSUNDEEP
sqloracle-database
Brett Cohen
Brett Cohen

Reputation: 29

Error report - ORA-02291: integrity constraint violated - parent key not found

so I'm writing some code in Oracle and have established the following tables: 

CREATE TABLE users
(
    user_id         NUMBER          NOT NULL,
    email_address   VARCHAR2(50)    NOT NULL    UNIQUE,
    first_name      VARCHAR2(10)    NOT NULL,
    last_name       VARCHAR2(10)    NOT NULL,
    CONSTRAINT users_pk PRIMARY KEY (user_id)
)


CREATE TABLE product 
(
    product_id      NUMBER,
    product_name    VARCHAR2(50)    NOT NULL,
    CONSTRAINT product_pk PRIMARY KEY (product_id)
)

CREATE TABLE downloads
(
    download_id     NUMBER, 
    user_id     NUMBER  NOT NULL,
    product_id NUMBER   NOT NULL,
    download_date   DATE NOT NULL,
    filename    VARCHAR2(50)    NOT NULL,
    CONSTRAINT downloads_pk PRIMARY KEY (download_id),
    CONSTRAINT downloads_fk 
        FOREIGN KEY(user_id) REFERENCES users (user_id),
    CONSTRAINT downloads_fk2
        FOREIGN KEY(product_id) REFERENCES product(product_id)
)

CREATE SEQUENCE user_id_seq
CREATE SEQUENCE download_id_seq
CREATE SEQUENCE product_id_seq

The downloads table connects the users and product table, containing the foreign keys user_id and product_id. I am trying to insert data into the downloads table via the following code: 

INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NEXTVAL,1,2, SYSDATE, 'one_horse_town.mp3')
INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NXTVAL, 2, 1, SYSDATE, 'pedals_are_falling.mp3')
INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NEXTVAL, 2, 2, SYSDATE, 'random_song.mp3')

Oracle then gives me the following error:

Error report - ORA-02291: integrity constraint (BC29369.DOWNLOADS_FK2) violated - parent key not found.

I am not sure why this is coming up, as it seems to me that I have clearly labeled the primary and foreign key relationships as I should. Does anyone know how to fix this? Thank you in advance!

Upvotes: 3

Views: 7400

Answers (1)

ArtBajji
ArtBajji

Reputation: 960

The constraints

CONSTRAINT downloads_fk FOREIGN KEY(user_id) REFERENCES users (user_id)

and

CONSTRAINT downloads_fk2 FOREIGN KEY(product_id) REFERENCES product(product_id)

means that the user_id values 1 and 2, the product_id values 1 and 2 which you are trying to insert into downloads table must already exist in users table and product table.

If users and product tables are not populated with these values (1 and 2) before you insert those values into downloads table, you will get this error as the integrity constraints will be violated.

You are creating an orphan record with no parent record. This is precisely what you were ensuring not to happen when you created those two constraints.

Upvotes: 2

Related Questions