Re: deleting primary keys???

Heyyyy, them's fightin' words. Oracle's got all the RI you could want, and if you can't do it with PK's and FK's, etc, well triggers are great.

As far as plum pudding goes, I love it; those commercial American fruitcakes (er, I do mean the baked variety) seem to me like either they're bought already stale, or they just don't get any worse. I guess the fruitcake companies must use SYBASE.

:)

RSH. "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:a5a1j0$ikn$1_at_lust.ihug.co.nz...
> As a lover of Christmas pudding, I've enjoyed enlightening wisdom from
this
> post. I always wondered why they tasted so good, and now I know:
> badly-implemented referential integrity!! I shall use SQL Server more
> often, I think...
>
> Cheers,
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message
> news:PT_d8.3728$FE4.214240_at_bgtnsc04-news.ops.worldnet.att.net...
> > This is where triggers and well documented and implemented RI
constraints
> > are for.
> >
> > In all rationality, (pardon the pun) you wouldn't want a situation where
> you
> > delete a, say, SEARS customer master record, linked to customer-orders.
> > order linked to the order-item table, which has order-item-quantity
rows,
> > leaving 10 nasty ancient Christmas fruitcakes unwanted, unshipped, and
> > undelivered to the deserving recipients.
> >
> > (Oh don't worry, they do not, so far as I know, ever spoil, as such.
Some
> > predate Egyptian mummies and are far better preserved.)
> >
> > Although this may explain the origin of many nasty fruitcakes and their
> > provenance.
> >
> > If you have some cleanup to do, approach it in a detail-upward fashion,
> > write some PL/SQL to do it, with logic to ensure referential integrity
is
> > preserved, and make some stored procedure or package with an entry point
> > like DELETE_CUSTOMER (argument) that traverses the logic tree backwards
> and
> > removes the master record last; logging to an audit trail table would be
> > highly advisable.
> >
> > I agree about cascading deletes, they can cause more problems than they
> > solve, and you don't have the ability to document and preserve data as
one
> > does with one's own procedures.
> >
> > RSH.
> >
> > But just don't hack at it manually, that will be no end of trouble.
> >
> >
> >
> >
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:a5970p$qrp$1_at_lust.ihug.co.nz...
> > > Quite right. You are not allowed to create orphans. So you will be
> > > prevented from deleting records in the parent table if there are
records
> > in
> > > the child table (the one with the foreign key) which relate to it.
> You'll
> > > get an ORA-02292: integrity constraint (SCOTT.EDFK) violated - child
> > record
> > > found if you try it.
> > >
> > > Of course, you can delete any parent records that have no children
> (though
> > > when you do so, be aware of horrible locking issues -which you can
read
> > > about on my site [see the Tips page, and then the Basic Administration
> > > stuff. There's a section on constraints there that describes the
> > problem]).
> > > So yes, one fix is to go to the child table and do manual deletes of
all
> > > records that relate to the parent record you really want to delete.
> > >
> > > However, there is a workaround: you can create the foreign key
> constraint
> > > with the 'ON DELETE CASCADE' keywords. The syntax (assuming two
tables
> > > called e1 and d1) runs like this:
> > >
> > > alter table e1 add (
> > > constraint edfk foreign key(deptno)
> > > references d1(deptno)
> > > on delete cascade);
> > >
> > > If you now delete a parent record that has children, you simply get:
> > >
> > > SQL> delete from d1 where deptno=10;
> > >
> > > 1 row deleted.
> > >
> > > Which is a complete lie, of course... because what it neglects to tell
> you
> > > is that it's gone off and deleted three records in the child table,
too!
> > (A
> > > count(*) from e1 -which is just a copy of ye olde standard emp table-
> > after
> > > this reveals a mere 11 records, not the 14 originals). Personally, I
> > > dislike automatic -and totally silent- deletes of child records (and
> > without
> > > even an 'are you sure?' prompt!) so much, I'd never use the 'on
delete'
> > > clause in a million years. But your mileage might vary!
> > >
> > > Regards
> > > HJR
> > > --
> > > ----------------------------------------------
> > > Resources for Oracle: http://www.hjrdba.com
> > > ===============================
> > >
> > >
> > > "AJG" <algroot_at_rogers.com> wrote in message
> > > news:LGUd8.4002$qQ.879_at_news2.bloor.is...
> > > > Hi,
> > > >
> > > > I'm new at the Oracle game so forgive me if this is a stupid
question.
> > I
> > > > have created tables and assigned primary and foreign key
constraints,
> > > > populated the data with using the INSERT INTO command and updated
> > several
> > > > records (i.e. UPDATE slsrep SET rate=.06 WHERE slsrnumb=3; etc...).
> > > >
> > > > I am now required to delete 1 record from each table, but as I
> > understand
> > > > it , if I delete a row that contains a primary key that is used as a
> > > foreign
> > > > key in another table I will get an integrity constraint error. So,
if
> I
> > > > delete the record in the table that contains the foreign key first
and
> > > then
> > > > the record in the table where it is a primary key, will this solve
my
> > > > problem, if it is indeed a problem? Is there a better way? Any help
> is
> > > > greatly appreciated. Thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Sun Feb 24 2002 - 10:27:51 CST