Re: sql question

In article <348D08F8.53A7_at_dimedis.de>,   gbrinkmann_at_dimedis.de wrote:
>
> john_at_rl.is wrote:
> > Never done this kind of stuff but it might be faster to do something
> > along the lines of:
> >
> > BEGIN WORK;
> > CREATE TABLE NewTable (...);
> > LOCK TABLE NewTable IN EXCLUSIVE MODE;
> > INSERT INTO NewTable SELECT DISTINCT * FROM OldTable;
> > DROP TABLE OldTable;
> > RENAME TABLE NewTable TO OldTable;
> > CREATE INDEX ...;
> > COMMIT WORK;
>
> What happens if there are (primary-key)-references to OldTable and it is
> dropped ? Are the references "reactivated" after the renaming ? Or is
> the state of the database inconsistent in any way afterwards ?
> ...

The point of the exercise was to eliminate duplicate rows that exist in OldTable. By definition this rules out the possibility of any primary or unique constraints on OldTable that can be used for reference by other tables. In other words, if OldTable had a primary key you wouldn't have this problem in the first place.

On the other hand there might be a bunch of stuff on OldTable like foreign keys, check constraints, triggers etc. which I didn't bother to mention, but they would have to be recreated e.g. when the indexes are created.

Best wishes,

John H. Frantz           Power-4gl: Extending Informix-4gl
frantz_at_centrum.is        http://www.rl.is/~john/pow4gl.html

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet