CODEWITHSUNDEEP
sqldatabase
oxygenpt
oxygenpt

Reputation: 368

Simple database. Foreign keys insertion

I have 2 categories A and B with the following columns:

Category A 

_id | name

Category B 

_id | tableA_id |name

Item List

_id | name | tableB_id

tableA_id and tableB_id are foreign keys and _id columns are primary keys.

Category B is a sub-category of A.

name columns are unique (there will be just 1 category in each table)

I want to insert 5 items each with their category A and B.

Item 1:

name: item1
category A: Movie
Category B: Action

Item 2:

name: item2
category A: Movie
Category B: Drama

Item 3:

name: item3
category A: Movie
Category B: Comedy

Item 4:

name: item4
category A: Music
Category B: Pop

Item 5:

name: item5
category A: Music
Category B: Rock

How can I populate the foreign keys without querying the ids of their correspondent primary?

Or am I missing an important part of DB design?

Upvotes: 0

Views: 69

Answers (3)

Tulains Córdova
Tulains Córdova

Reputation: 2639

First populate CATEGORY_A and CATEGORY_B.

Then insert into ITEM_LIST knowing CATEGORY_B.ID.

Upvotes: 1

bpgergo
bpgergo

Reputation: 16047

How can I populate the foreign keys without querying the ids of their correspondent primary?

If I understand your question correctly, you cannot populate the foreign keys when inserting new record into table List without querying the ids of Rock, that is without

select _id,  tableA_id
from Category_B
where name = 'Rock'

Upvotes: 1

Steve Wellens
Steve Wellens

Reputation: 20640

If your database design is 'correct', to prevent corruption you will not be able to insert child records without their parent keys.

In order to get the parent key, without doing a re-query, read this excellent post:

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Upvotes: 1

Related Questions