Cannot insert row in Oracle DB table with FOREIGN KEY-CodePudding

I want to insert row to table with this structure:

CREATE TABLE table_name1 (
        id_name1 NUMBER,
        id_name2 NUMBER,
        id_name3 NUMBER,
        datevalue TIMESTAMP,
        value_name1 NUMBER,
        PRIMARY KEY (id_name1),
    FOREIGN KEY (id_name2) REFERENCES table_name2 (id_name2),
        FOREIGN KEY (id_name3) REFERENCES table_name3 (id_name3)

table_name2 is empty table_name3 have some data

Insert query:


INSERT INTO table_name1 (
    id_name1, 
    id_name2, 
    id_name3, 
    datevalue,  
    value_name1
)
VALUES (
    1, 
    1,  
    1, 
    TO_TIMESTAMP('2020-07-03 13:29:00', 'YYYY-MM-DD HH24:MI:SS'), 
    1
)

While executing query i have this error:

SQL Error [2291] [23000]: ORA-02291: integrity constraint violated (SYSTEM.SYS_C008315) - source key not found

Error position: line: 1

I think the problem in inserting values to table with foreign keys. Who can say what the problem is?

CodePudding user response：

The answer has largely been given in the comments, but trying to write it down.

In order to have a foreign key, the table you are referring to has to exist (which makes your example somewhat incomplete as the other two tables are not created) and in addition any key that you want to refer to has to exist before you can insert things into your table_name1.

To write this down as a somewhat complete example you could stick into an sqlfiddle also:

CREATE TABLE table_name2 (
        id_name2    NUMBER,
        value_name2 NUMBER,
        PRIMARY KEY(id_name2)
);

CREATE TABLE table_name3 (
        id_name3    NUMBER,
        value_name3 NUMBER,
        PRIMARY KEY(id_name3)
);

CREATE TABLE table_name1 (
        id_name1 NUMBER,
        id_name2 NUMBER,
        id_name3 NUMBER,
        datevalue TIMESTAMP,
        value_name1 NUMBER,
        PRIMARY KEY (id_name1),
        FOREIGN KEY (id_name2) REFERENCES table_name2 (id_name2),
        FOREIGN KEY (id_name3) REFERENCES table_name3 (id_name3)    
);

Note that the order matters in that table_name2 and table_name3 have to be created before table_name1 as otherwise the REFERENCES obviously has nothing to refer to.

To insert/select

INSERT INTO table_name2 (
    id_name2, 
    value_name2
)
VALUES (
    2, -- id_name2
    42
  );


INSERT INTO table_name3 (
    id_name3, 
    value_name3
)
VALUES (
    3, -- id_name2
    999
 );

INSERT INTO table_name1 (
    id_name1, 
    id_name2, 
    id_name3, 
    datevalue,  
    value_name1
)
VALUES (
    1, -- id_name1
    2, -- id_name2
    3, -- id_name3
    TO_TIMESTAMP('2020-07-03 13:29:00', 'YYYY-MM-DD HH24:MI:SS'), 
    1
);

select * from table_name1;
select * from table_name2;
select * from table_name3;

also here order matters as you first have to have the keys in table_name2 and table_name3 before you can insert (and thus refer to them) in table_name1