Thread: FOREIGN KEY

Hi,

Foreign Keys are implemented with triggers and you can list thim all by doing a 'SELECT * FROM pg_trigger;'.   The triggers starting with RI_ConstraintTrigger_?????? are those in question, but which trigger goes with which foreign key is not obvious.  If you have named the Foriegn keys then it should not be too much problem.  Otherwise you should me able to make it out from the tgargs field.  When you have found out which triggers you need to drop use DROP TRIGGER ... to remove them.

You can add the Foreign keys again after you have copied all the data with ALTER TABLE ...

Hope this helps

Ben

> -----Original Message-----
> From: Andras Balogh [mailto:abalogh@grafx.ro]
> Sent: 04 July 2001 14:46
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] FOREIGN KEY
>
>
> Hi,
>
> I have created a table is postgres with a FOREIGN KEY reference to
> another table.
> I am using the COPY command to copy the data from a file to this table.
> I am getting this error:
> referential integrity violation - key referenced from A not found in B
>
> I understand that I am inserting a row in A with an id that is not in table B,
> I want to ignore those inserts that are violating the referential integrity.
> What should i do? 
>
> Aslo i didn't find a way to list the FOREIGN KEYS in a table
> \d <tablename> it gives only the structure.
>
> Any input is welcomed,
>
> Andras.

*****************************************************************************
This email and any attachments transmitted with it are confidential
and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please
notify the sender and do not store, copy or disclose the content
to any other person.

It is the responsibility of the recipient to ensure that opening this
message and/or any of its attachments will not adversely affect
its systems. No responsibility is accepted by the Company.
*****************************************************************************


From: "Chuck Shunk" <cshunk@shentel.net>

RE: [GENERAL] FOREIGN KEYHello everybody,

>This is my first time posting to this list, so forgive me if my question
seems dumb or >ill informed.


Question is clear, succinct and complete :-)
Email is in HTML rather than raw text :-(

>    INSERT INTO remote_sales_table VALUES
>        (SELECT sales_info, local_terminal_id FROM local_sales_table WHERE
uploaded =
>'n')

>However, as the two tables reside in two separate databases, that won't
work, will it?
>So, my question is, what is the best way to synchronize a master database
with multiple
>client databases?  Right now, I have a c++ routine that does the local
select to get
>all the sales data and then steps through each row and builds an insert for
the master
>server.  Is there a more elegant solution?

Not really - I had to do something similar recently and came up with a
similar solution. One thing that seemed to work for me was to have a
separate import_xxx_table along with my xxx_table. The external code
concentrated on checking the transport of the data and just stuffed it into
import_xxx_table. It then called a plpgsql function that did all the actual
work of importing/updating. There was the mirror image for the export
functionality.

This kept my interfaces nice and clean and kept all my data-munging inside
the database. The downside was plpgsql's error reporting leaves a little to
be desired, so debugging was a pain. If repeating the project, I would build
the system with a noddy setup and add the detail in step by step.

>If there is not a better solution already existant, I am going to build
some generic
>procedures and objects in c++ for synchronizing tables of this sort.  Is
anyone else
>interested in a project like this?  I'll have a project up on sourceforge
in a bit.

Check the "contrib" folder of the source distribution and have a look at the
replication code. Actually, I think there are two or three different
replication porjects out there, so perhaps have a rummage on google.

>By the way, this POS terminal project I'm talking about is already up on
sourceforge
>(it's called poskiosk), but please only look at the code if you're really
interested--I
>would be terribly embarrassed!  (It's kind of pathetic at this stage)

Any code is a start, and most of the people throwing rocks never write a
line of it.

- Richard Huxton

Hi Justin,

I'm doing this for a company that controls a number of mall kiosks selling
Cell phone accessories.  They want to be able to keep track of sales and
inventory from their home office on a day-by-day basis, and they are finding
this rather difficult with locations spread out throughout several states.
The POS terminals I'm designing will upload sales info by dialing into a
central server and populating the central database.  They will also download
things like employee timesheets and new product info.

Sorry about the HTML format email--I thought I had configured Outlook
Express to send plain text--anybody know how to fix that?  I can't find any
other options to select for just plain text.

Thanks,

Chuck
----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Chuck Shunk" <cshunk@shentel.net>
Sent: Thursday, July 05, 2001 9:09 AM
Subject: Re: [GENERAL] Remote queries


> Hi Chuck,
>
> This POS Kiosk sounds interesting.
>
> What type of thing are you guys doing with it exactly?
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>