Thread

1. pg_dump problem

   Paulo Roberto Siqueira <paulo.siqueira@usa.net> — 2000-08-04T01:05:28Z

   	PgSQL 7.0.2
   
   	I used pg_dump to dump the schema of a db. I created table part_presencial
   with this command
   
   create table part_presencial(
   login char(15) not null,
   id_curso_polo int4 not null,
   data_encontro date not null,
   nota numeric(2,2) null,
   foreign key(login,id_curso_polo) references matricula,
   foreign key(id_curso_polo,data_encontro) references presencial,
   primary key(login,id_curso_polo,data_encontro));
   
   	But pg_dump gives me this:
   
   CREATE TABLE "part_presencial" (
      "login" char(15) NOT NULL,
      "id_curso_polo" int4 NOT NULL,
      "data_encontro" date NOT NULL,
      "nota" numeric(2,2),
      PRIMARY KEY ("login", "id_curso_polo", "data_encontro")
   );
   
   Why doesn't pg_dump give me foreign keys constraints? It happens to all the
   tables with foreign keys.
   
   
   -----
   Paulo Roberto Siqueira
   paulo.siqueira@usa.net
   Database Administrator
   Goiania - GO - Brazil
   
   
   

2. Re: pg_dump problem

   Philip Warner <pjw@rhyme.com.au> — 2000-08-04T13:26:07Z

   At 22:05 3/08/00 -0300, Paulo Roberto Siqueira wrote:
   >
   >Why doesn't pg_dump give me foreign keys constraints? It happens to all the
   >tables with foreign keys.
   
   pg_dump does not restore the database by executing the same set of commands
   as you used to create the database. Foreign key constraints are implemented
   using a special kind of trigger (CONSTRAINT TRIGGER). If you look through
   the pg_dump output, you will find a number of 'CREATE CONSTRAINT TRIGGER'
   statements which will reapply the foreign key constraints.
   
   This is definitely not an ideal situation, and with time I hope that
   pg_dump output will become as close to ISO SQL as possible. But in the
   example you quoted I would expect pg_dump to dump a basic 'create table'
   statement followed by several 'alter table add constraint...' statements,
   not one complex table definition.
   
   Hope this helps,
   
   Philip Warner.
   
   
    
   ----------------------------------------------------------------
   Philip Warner                    |     __---_____
   Albatross Consulting Pty. Ltd.   |----/       -  \
   (A.C.N. 008 659 498)             |          /(@)   ______---_
   Tel: (+61) 0500 83 82 81         |                 _________  \
   Fax: (+61) 0500 83 82 82         |                 ___________ |
   Http://www.rhyme.com.au          |                /           \|
                                    |    --________--
   PGP key available upon request,  |  /
   and from pgp5.ai.mit.edu:11371   |/