CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Bumbes Robert
Bumbes Robert

Reputation: 11

SQL - Multiple entries for foreign key

This is my first time here, don't know exactly how it goes. :) I have a project at school for Databases, we use SQL in SQL Developer, with oracle express 11g. I chose to do a DB for a music festival and I have some problems with it. I'm having trouble populating the tables, I think it's mainly a design fault.

I have a table Music Festival which I wanted to use as a main table from which each table collapses into another one. What I don't understand is how I populate the tables for the ONE-TO-MANY relationships. From the MUSIC FESTIVAL table I have a ONE-TO-MANY relationship with the PUBLIC table which contains the info about every concert-goer. I put the TICKET_ID as a primary key in PUBLIC so I can use it as a Foreign Key in MUSIC_FESTIVAL, but when I got to insert the entries into the table I just got stuck.

Here it's the ERD to make it easier to understand:

enter image description here

And this is how it looks in SQL DEVELOPER:

SQL DEVELOPER

This is the error I'm getting: One error saving changes to table "ROBERT"."PUBLIC": Row 1: ORA-02291: integrity constraint (ROBERT.PUBLIC_MUSIC_FESTIVAL) violated - parent key not found ORA-06512: at line 1

Also, I find it difficult to populate the tables because of those errors. SQL DEVELOPER doesn't let me enter the data on one table and after that go to another table to solve the errors.

Please help, I really need some.

Have a nice day!

Upvotes: 1

Views: 193

Answers (1)

The Impaler
The Impaler

Reputation: 48780

As a general rule of thumb you need to insert data in order. That is, insert into the parent first, then into the children table(s). Well... unless you are using deferrable constraints, but that's a fairly advanced subject you should avoid for now.

For example:

insert into MUSIC FESTIVAL (name, location, date, stages, tickets, transportation)
  values ('Monster', 'Tokio', date '2019-03-15', 12345678, 12);

insert into PUBLIC (public_name, ..., ticket_id, ...)
            values ('Abc', ...,       'Monster', ...);

The second insert, on the child table, includes the foreign key Monster already created by the first insert.

Upvotes: 2

Related Questions