Re: steps to ensure referential integrity - Mailing list pgsql-admin

________________________________
Hi Jodie,

    This snippet is generated code from my client side data dictionary for
postgresql.
    The sequence described is used with nextval() to obtain autoincrement
key values
    for the primary key. Thus primary key definition does create an index as
do foreign key
    declarations.  I use the views to mimic conditional indices. Each view
has a companion
    index generated if one suitable is not already in existance.

    HTH
    Mark Carew
    Brisbane Australia
    07 35117911


      /*  client side     */

    IF ascan(aTokens,"AUTOINC") > 0
      *
      ::AutoInc  := TRUE
      *
      IF cColumnType == "CS"
        *
        #IFDEF SQLFILES
          *
          ::SqlDefault := ;
            "SELECT nextVal('" + ;
            lower(::oTable:filename) + ;
            "_seq') ;"
          *
        #ENDIF
        *
      ENDIF
      *
    ENDIF

  /*  server side */




    DROP SEQUENCE invhdr_seq ;
    CREATE SEQUENCE invhdr_seq START 322 ;

DROP TABLE invhdr ;

CREATE TABLE invhdr  (
    vinno  int4,
    vindate  date
        NOT NULL,
    dateent  date,
    clientno  char(12)
        NOT NULL,
    total  numeric(10,2),
    balance  numeric(10,2),
    allocamt  numeric(10,2),
    gst  numeric(9,2),
    cordno  char(14),
    printed  char(1)
        ,
    origin  char(1)
        ,

    PRIMARY KEY ( vinno )

    ,FOREIGN KEY (clientno )
        REFERENCES client
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
    );

    CREATE INDEX invhdr_dateclt ON invhdr (vindate, clientno);

    CREATE INDEX invhdr_vindate ON invhdr (vindate);

    CREATE INDEX invhdr_dateent ON invhdr (dateent);

    DROP VIEW invhdr_unprint_view ;

    CREATE VIEW invhdr_unprint_view AS
     SELECT * FROM invhdr
      WHERE PRINTED = 'N'
       ORDER BY vinno ;

    CREATE INDEX invhdr_clntactv ON invhdr (clientno, vindate);

    DROP VIEW invhdr_clntactv_view ;

    CREATE VIEW invhdr_clntactv_view AS
     SELECT * FROM invhdr
      WHERE BALANCE  <> cast( 0.00 as numeric )
       ORDER BY clientno, vindate ;

    CREATE INDEX invhdr_cordno ON invhdr (cordno);

    DROP VIEW invhdr_cordno_view ;

    CREATE VIEW invhdr_cordno_view AS
     SELECT * FROM invhdr
      WHERE CORDNO <> ' '
       ORDER BY cordno ;