Re: self-referential integrity?
Mark Urquhart-Webb wrote:
>
> I have two tables and I want to identify (and then delete) rows from
> the subordinate table that does not have a match in the primary table.
>
> eg.
>
> Customers (cust_code char(4), name char(20))
> Orders (cust_code char(4), ord_date date, etc etc))
>
> So, if a Customer has been deleted, how can I identify (prior to
> deleting)
> the orders that do not have a customer record?
>
> delete from orders where
> where orders.cust_code 'does not match any records in the Customer> table'
>
> This is a simplistic version of events, inactuality my database has
> about seven levels of lookup;
> Contracts=>Customers=>Contacts=>Orders=>Order_Lines
> etc etc
Mark,
what you are describing is the classical referential integrity situation
but there is nothing self-referential about it.
To keep it simple, let's keep to the 2-tier master-detail relation where
the DBA failed to implement referential integrity, allowing an order
record to be created for a non-existent customer. Similarly, this
allows a customer record to be deleted while leaving an orphaned orders
record in place.
Step 1: Feed the DBA to the Minotaur of Crete. Have a transmitter
attached to the DBA and record the event as a warning to the
next DBA.
Step 2: delete from orders
where cust_code
not in (select cust_code from customer
where cust_code is not null)
BTW, the "where cust_code is not null" in the subquery should not be
necessary. However, the careless DBA prbably neglected to include a NOT
NULL constraint on the [functional] primary key column and (as I have
learned through harsh experience) nulls can wreak havoc on the results
of a NOT IN (subquery).
If you have 7 levels of hierarchy, start with the lowest level and, one
delete statement at a time, drom all rows with no corresponding parent.
i.e.
delete from order_lines where order_num not in
(select order_num from orders where order_num is not null)
Oh, *you* are the DBA? Well, er... just run step 2. ;-)
Good luck. And maybe you should avoid Crete this year.. ;)
--
-- Jake (Never yelled "CROWDED THEATER!" during a fire)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+