On Tue, 2003-09-23 at 13:41, [EMAIL PROTECTED] wrote:
> On Tue, Sep 23, 2003 at 10:52:27AM -0600, scott.marlowe wrote:
> > On Mon, 22 Sep 2003, Jodi Kanter wrote:
> >
> > > I have inherited a database that was originally created in postgres
> > > version 6.? There were no steps taken at that time to implement
> > > constraints to ensure referential integrity. There are no FK
> > > constraints, PK constraints, etc..
> > > Most of what currently exists is done in the existing code. I would like
> > > to change this and am interested in getting suggestions. I expect that
> > > PK constraints on each table as well as all FK constraints should be in
> > > place. Anything else that I should consider?
> > > I have indexes currently set up on a variety of fields, including PK
> > > fields. Do the constraints automatically generate indexes on these
> > > fields such that I should remove the ones created at the end of my script?
> > > Thanks in advance for the suggestions!
> >
> > I would first upgrade to a reasonably new version of Postgesql 6.5.x is
> > pretty old. 7.3.4 or so should be a good choice. You'll find that you
> > can add the pk/fk constraints in the conversion process, and still have a
> > faster database with fk/pk under 7.3 than you'd have with no fk/pk
> > constraints in 6.5.3
> >
>
> Creating a primary key also creates a unique index on that field(s). Depending on
> how normalised the data schema is, you may also have to create stored procedures to
> gaurentee data integrity. If the data is not currently good, you may have to remove
> bad data to get the constraints to check. I'm no guru, but I do something like this
> to at least check:
>
> select count(distinct(id)) from table;
> vs
> select count(id) from table;
>
select count(distinct(id)), count(id), count(*) from table
> if the numbers are different, you have dup ids.
>
> To get a list:
> select id,count(*) from table group by (id);
>
> I hope someone can figure out how how to put a where on that so it only returns
> those above 1, but I personaly don't know how, so I'll use my friend the shell:
>
select id,count(*) from table group by id having count(*) > 1;
and actually just running that would allow you to skip the check from
above.
HTH
Robert Treat
> psql database -Uuser -c 'select id,count(*) from table gropu by id' | grep -v '1$';
>
> Alex Turner
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
