Thread: Foreign Key Problem

Foreign Key Problem

From

Date:

02 May 2005, 18:41:52

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

Re: Foreign Key Problem

From

Tom Lane

Date:

03 May 2005, 02:19:02

<operationsengineer1@yahoo.com> writes:
> 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.

Hmm, this is definitely a bug in pgadmin: it should be ignoring dropped
columns.   Perhaps you have an old version of pgadmin?  If it's current,
you should report the problem to the pgadmin list.

            regards, tom lane

Re: Foreign Key Problem

From

Date:

03 May 2005, 02:24:30

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> <operationsengineer1@yahoo.com> writes:
> > 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.
>
> Hmm, this is definitely a bug in pgadmin: it should
> be ignoring dropped
> columns.   Perhaps you have an old version of
> pgadmin?  If it's current,
> you should report the problem to the pgadmin list.
>
>             regards, tom lane
>

tom, i don't know if this is a pgadmin display issue
or if it a pgsql issue.

if i don't get some guidance, i will likely delete
both the tables entirely and rebuild them from
scratch.

the behavior of trying to verify foreign key matches
in the wrong table - either b/c pgsql is screwing up
or, much more likely, i'm screwing up.

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

Re: Foreign Key Problem

From

Tom Lane

Date:

03 May 2005, 03:10:59

<operationsengineer1@yahoo.com> writes:
> tom, i don't know if this is a pgadmin display issue
> or if it a pgsql issue.

The presence of a dropped column in that dropdown menu is certainly
a pgadmin bug.  I suspect that the bug may be leading to other
misbehaviors, say for instance the display of which columns reference
which might be offset by 1 because of the bogus appearance of the
dropped column in the list.  But that is strictly a guess.

You should get some pgadmin hackers involved before doing anything else.
In particular, I'd counsel against dropping the tables involved before
a fix has been verified, because it could be that there are more
contributing factors than just "you dropped a column".  If you wipe the
tables now you might be destroying evidence that is needed to fix the
bug.  (And if the bug is indeed on the backend side rather than in
pgadmin, that advice goes double.)

            regards, tom lane

Re: Foreign Key Problem

From

Date:

03 May 2005, 04:43:25

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> <operationsengineer1@yahoo.com> writes:
> > tom, i don't know if this is a pgadmin display
> issue
> > or if it a pgsql issue.
>
> The presence of a dropped column in that dropdown
> menu is certainly
> a pgadmin bug.  I suspect that the bug may be
> leading to other
> misbehaviors, say for instance the display of which
> columns reference
> which might be offset by 1 because of the bogus
> appearance of the
> dropped column in the list.  But that is strictly a
> guess.
>
> You should get some pgadmin hackers involved before
> doing anything else.
> In particular, I'd counsel against dropping the
> tables involved before
> a fix has been verified, because it could be that
> there are more
> contributing factors than just "you dropped a
> column".  If you wipe the
> tables now you might be destroying evidence that is
> needed to fix the
> bug.  (And if the bug is indeed on the backend side
> rather than in
> pgadmin, that advice goes double.)
>
>             regards, tom lane
>

tom, thanks for the heads up.  i will make a copy of
the db and then work on the copy.

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

Re: Foreign Key Problem

From

Date:

03 May 2005, 19:44:04

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