quick question from a newbie

[swidler]

i'm setting up a small database with 3 tables: customers, recipients, and orders. each of them has an auto increment primary key. the orders table also has fields for the primary key of each of the others (using the same type--integer). i connected them seemingly successfully by these fields. when i try to enter values into these two fields (no, i'm not using forms), i get an error:
SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent SYS_FK_101 table: recipients in statement [INSERT INTO "orders" ( "customer number","paid","quantity","recipient number","type") VALUES ( ?,?,?,?,?)]

can anyone explain this (and tell me how i should be setting this up)? tia.

[Sliderule]

You have set up some database RELATIONSHIPS between your tables:

1. "customers"
   
2. "recipients"
   
3. "orders"

According to the error message . . . the "orders" table canNOT contain a new record where there is not yet a record in the "recipients" table. ( The SYS_FK_101 . . . FK stands for Foreign Key )

I would assume this is probably because no matching "recipenent number" yet in the "recipients" table. This is how a relational database works to enforce relational integrity.

Solution: Before inserting the record in the "orders" table, be sure that a matching record exists in the "recipients" table ( and if necessary . . . also in the "customers" table ).

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

[swidler]

except that i didn't start the orders table until the other two were full (but thanks for the explanation). i even wiped and redefined it (including the relationships). the number i used for both foreign keys was 0. since that's the first auto increment number base assigned (could 0 cause a problem?). incidentally, 2 ones gave the same error.

[Sliderule]

I am sorry but I do not understand your question.

When you say "the other two were full" . . . do you mean you already inserted the two new reocrds?

What do mean by "wiped and redefined it" . . . if you are using an autoincrement field . . . this is just an internal counter. Even if you delete, for example ID = 0, ID = 1 and ID = 2 . . . the NEXT value will NOT restart with 0 . . . UNLESS . . . you have issued an explicit database command to RESTART it . . . and . . . you have issued a valid number ( greater than any still in the table ).

[swidler]

sorry i wasn't clear. i filled the customer and recipient tables. then i tried to enter values in the orders table. no luck. so i deleted the orders table and redefined it completely. still no luck.

[Sliderule]

I do not know what to tell you, exactly, because I can not see it.

You have an ability to TURN OFF REFERENTIAL INTEGRITY check . . . temporarity, if you want . . . BUT . . . this should be a 'last resort'.

The command is issued from the Menu:

Tools -> SQL...

[swidler]

this doesn't sound like a great idea. i feel like this should be a simple task. is there an easy way i can show you the db? it's quite small.

[Sliderule]

I am sending you a PM ( Private Message ).

Sliderule

[swidler]

ok, looks like i was wrong. all the work i did filling the recipients table seems to have disappeared. i guess that was when the thing hung and i thought it was all recovered. i supposed i should have checked. so i'm assuming that after i repopulate the damn thing, all will be well. if/when that doesn't happen, you'll see me back here. thanks for the help.

[Sliderule]

OK.

At least you have found the 'root' problem . . . so . . . you know how to proceed.

Please . . . for OTHERS reading the forum . . . I would appreciate it very much if you could go to your FIRST (top ) post here in the forum.

In the Upper Right hand corner is an EDIT button. Click on it and add the word [Solved] to the forum post Title.

This is just so others will know a solution has been found . . . if / when they are looking for similar issues.

Sliderule