Problems with inserting data

[sugafree 0]

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?

[davej 251]

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?

[shreyaskudav 2]

I think stocklist_id would become your foreign key.. Try changing the name of the column stocklist_id to something else!

[sugafree 0]

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

[davej 251]

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

[sugafree 0]

Thank you Davej, would you sell any of your knowledge?? If braincells can be transferred, I would be happy to buy just a small piece of it, that would be more than enough for me..