Re: SQL question

deanbrown3d wrote:
> Suppose I have a table A that has a field PARENT_SEQ_I that is a FK
> onto the same table field SEQ_I.
>
> Also, suppose that the parent record in the 'group' has PARENT_SEQ_I
> set to point to its own SEQ_I.
>
> Can I "delete from A where PARENT_SEQ_I = x"?
>
> The question is, if the parent record is deleted first, then the FK
> constraints on the other records will throw an error. However, if the
> child records that point to the parent are deleted first, and THEN the
> parent is deleted, all will be ok. So, will it fail sometimes if I let
> oracle randomly choose the order of things to delete?
>
> Thanks
>
> Dean

Take a look at the following syntax:

ALTER TABLE person
ADD CONSTRAINT fkocd_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE CASCADE; and

ALTER TABLE person
ADD CONSTRAINT fkdsn_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE SET NULL; Might these help?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)