Re: Delete Rules

Hi.

 You defined a foreign key without "ON DELETE CASCADE"  clause. The default behavior is - Oracle does not  permit delete of parent table row while any children  table row(s) references it.
 To change that you have :

1. Find the name of foreign key constraints ( both for cinema and movi tables ).
2. Drop both of them. E.g. ALTER TABLE cinema_movie DROP CONSTRAINT cinema_fk; ALTER TABLE cinema_movie DROP CONSTRAINT movie_fk;
3. Recreate these constraints with ON DELETE CASCADE : ALTER TABLE cinema_movie ADD CONSTRAINT movie_fk FOREIGN KEY ( movie_id ) REFERENCES movie ON DELETE CASCADE; ALTER TABLE cinema_movie ADD CONSTRAINT cinema_fk FOREIGN KEY ( cinema_id ) REFERENCES cinema ON DELETE CASCADE;

  HTH. Michael.

In article <380715CA.19F3EE0C_at_filmnet.gr>,   Ermina Chatzipavli <echatzip_at_filmnet.gr> wrote:
> Dear all,
>
> I have the following question:
>
> One table contains cinemas (cinema_id, .......), another one movies
> (movie_id,....) and the third one foreign keys (cinema_id, movie_id),
> since we have a many to many relationship (one cinema may play several
> movies, and one movie can be played in several cinemas as well).
>
> Now, I want to be able to delete all the records from the first or the
> second table but an error appears: integrity constraint violated -
> child record found. Is there a way to be able to delete a cinema or a
> movie and have all the related records in the third table deleted as
> well?
>
> I know there are some delete rules (cascade, restrict) but I don't
know
> how to use them. (I only used them when I created my database schema
in
> ERwin).
>
> Thank you in advance,
>
> Ermina
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 15 1999 - 08:13:21 CDT