Jump to content

Problems with inserting data


sugafree

Recommended Posts

Hi, I have created a database but when I try to insert data in oracle, I get an error ORA-02291: integrity constraint (T.BRANCHES_MEDICINE_STOCK_FK) violated - parent key not found. This branches medicine stock fk is above the relation between branches and medicine table. I used

 

INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b01','London Road','mst01,mst02','cd01');INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b02','Loughborough Road','mst03,mst04','cd02');INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b03','Newark Street','mst05,mst06','cd03');INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b04','Marsden Lane','mst07,mst08','cd04');INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst01','Nurofen',1566);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst02','4Head',933);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst03','Nurofen',455);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst04','4Head',152);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst05','Nurofen',1012);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst06','4Head',752);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst07','Nurofen',8);INSERT INTO Medicine_Stock (stocklist_id, medicine_name, amount_in_stock)    VALUES ('mst08','4Head',350);

As I used an sql script, the order shouldnt matter if im right, I declared all primary keys in both tables, branches and medicine stock, what else do i need to do?

Link to comment
Share on other sites

Why are you trying to insert two stocklist_ids in the same insert?

 

And why have you posted 12 insert statements but no information on the foreign key constraints or the table definitions?

Link to comment
Share on other sites

In my sample, each branch has 2 medicines, each medicine will have a stocklist with the PK stocklist_id. Should I add each stocklist_id separately? Also do i need to repeat the insert into statement for each line or can i just do it for each table like

insert into branches (...)

values

values

values?

 

I uploaded the ddl in a txt. Could you give me any comment on that? Also it should not be enhanced as this is quite close to what I have to cover and that is all i have got to do, just need to make it work. I have to populate the tables and have a few sql commands to retrieve different data. Job role will be deleted, instead i will create sub-types of employees such as managers, cleaners etc.. Im working on that now

 

sql.txt

Link to comment
Share on other sites

OK, so then isn't stocklist_id just a short name for each unique item that is kept in stock? Shouldn't each such item be inserted individually like this...

INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b01','London Road','mst01','cd01');INSERT INTO Branches (branch_id, branch_name, stocklist_id, contact_id)    VALUES ('b01','London Road','mst02','cd01');

You can search here for a shorter form...

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF55010

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...