Re: Deleting all data from tables

From: Alex <albu_at_my-deja.com>
Date: Fri, 10 Sep 1999 17:22:03 GMT
Message-ID: <7rbenb$l2l$1@nnrp1.deja.com>

Thank you everybody for the suggestions. We need the operation because we are in the initial phase of a project and we need to populate our tables with test data. And until we have some good test data...

I have a FK that points to another column of the same table. Something like:
EMP(empssn, name, mgrssn)

      ^              |
      +--------------+

where mgrssn is a foreign key that points to empssn. Can data be inserted without disabling referential integrity constraints (empssn is a PK)? Probably only is the first record has empssn = mgrssn. Is that right?

Alex

In article <7r9ahe$5p5$1_at_bgtnsc03.worldnet.att.net>,   "KM" <skmok_at_worldnet.att.net> wrote:
> I noticed a couple of good solutions have been posted already. Just
want to
> caution you that if what you described has to be done on a regularly
basis,
> you might want to re-examine the design of the database. In
particular,
> having a FK pointing to itself is worth looking at. Also, in general,
you
> shouldn't need to relax Referential Integrity to modify your tables on
a
> regular basis.
>
> Alex <albu_at_my-deja.com> wrote in message
news:7r8rnh$nvc$1_at_nnrp1.deja.com...
> > What's the best way to delete all data from a user's tables, while
> > keeping the table structure? Simply running DELETE FROM <table_name>
for
> > all tables will not work because of a myriad of integrity
constraints
> > without the ON DELETE CASCADE.
> >
> > Dropping all children before the parents doesn't seem appealing,
because
> > the solution isn't general. Also, how would this handle the case
where a
> > column that is a foreign key references a column in the same table?
> >
> > One solution I can think of is to write a PL/SQL procedure that gets
all
> > integrity constraints for the user, drops them, gets all tables,
deletes
> > data from them, and creates the integrity constraints again.
> >
> > Are there more elegant solutions? Any idea would be appreciated.
> >
> >
> > Alex
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 10 1999 - 12:22:03 CDT