Re: Foreign Key Problem - Mailing list pgsql-novice

okay, i rolled on this one.

$query = "INSERT INTO
t_link_product_base_assembly_notes " .
"(product_number, assembly_notes_id) " .
"VALUES (" . $db->qstr($id) . ", " . $db->qstr($V) .
")";

$id is the note id - being inserted into the product
number table

$V is the product id being inserted into the assembly
note table.

the database acted correctly - my code was gaffed.

i'm still not sure why pgadmin acts as does, however,
i can now play around with my db again and possibly
narrow down the issue to being a display issue or a
functional issue.


--- operationsengineer1@yahoo.com wrote:
> i have the following code...
>
> -------------
>
> $assembly = $_POST['product_base'];
> $assembly_notes = $_POST['assembly_notes'];
>
> $db = &ADONewConnection('postgres');
> $db ->
> Connect($db_string,$db_owner,$db_pw,$db_name);
>
> $assembly_notes_insert_id = $db->getone("select
> nextval('t_assembly_notes_assembly_notes_id_seq')");
>
> $sql = "INSERT INTO t_assembly_notes
> (assembly_notes_id, assembly_notes,
> assembly_notes_entry_date) VALUES (" .
> $assembly_notes_insert_id . ", " .
> $db->qstr($assembly_notes) . ", " .
> $db->DBDate(time()) . ")";
>
> $result_notes = $db->Execute($sql);
>
> //insert ids into link table
>
> $id = $assembly_notes_insert_id;
>
> if (isset($_POST['product_base']) and
> is_array($_POST['product_base']))
> {
> foreach($_POST['product_base'] as $V)
> {
> echo "Value: $V<br>\n";
>
> $query = "INSERT INTO
> t_link_product_base_assembly_notes " .
> "(product_number, assembly_notes_id) " .
> "VALUES (" . $db->qstr($id) . ", " . $db->qstr($V) .
> ")";
>
> $result_link = $db->Execute($query);
> }
> }
>
> -----------
>
> everything works fine except for my foreign keys.
>
> this code checks to see whether my assembly_notes_id
> is in my product table and checks whether my
> product_number is in my assembly_notes table - the
> opposite of what i want to happen.
>
> my link table has its assembly_note_id referencing
> the
> assembly_note_id in the assembly_notes table.
>
> my link table has its product_number referencing the
> product_number in the product table.
>
> i'm at a loss b/c i don't understand this behavior.
>
> ----------
>
> i've created a foreign key in pgadmin 3.
>
> i have a link table that links to product_number and
> assembly notes.  the reason for the link table is so
> i
> can allow one note to be attached to more than one
> product_number.
>
> i created a foreign key in pgadmin 3 bwteen the
> product_number in the link table and product_number
> in
> my product table.
>
> when i go to the pgadmin 3 constraints folder, open
> this foreign key and click on the columns tab, it
> shows conflicting information.
>
> Local says "product_number"
> Referenced says "product_number"
>
> so far, so good.
>
> Referencing says ".......pg dropped 1......." in the
> drop down menu.  i previously had to drop my first
> created column.
>
> when i first set it up, i made sure to choose
> �product_number from the drop down menu, but it
> defaults back to ".......pg dropped 1......."
>
> i'm not sure if this is related to my problem, but
> too
> much information is better than not enough
> information.
>
> tia...
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com