Re: steps to ensure referential integrity

________________________________
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 ;