Deleting spouses

Once a spouse is created, is appears that there is no way to delete
that person from the family_relatives table, since each spouse
references the other.

To delete one, I suppose that you must delete both at the same time,
and then immediately add the other back.

How do you delete a pair of rows each referring to the other at the
same time, so that the "references" constraint is not violated? is it
possible? or is marriage forever in this data model, even if by
accident?

-- Bobby Prill, March 6, 2000

Answers

This is how I did. First find the spouse(the one that will remain) of the spouse that you are trying to delete. Then update the corresponding position with " set spouse='' " in your sql statement. And then finally delete the row you wanted to get rid of initially.

One main thing to remember is to do the whole operation inside a "begin transaction" and "end transaction". You don't need two db handles.

-- Koroush Iranpour-Tehrani, March 6, 2000

It is also possible to simultaneously drop the inter-dependent rows. From Tcl you would do something like:

ns_db dml $db "delete from family_relatives
where relative_id in ([join [split $list_of_relative_ids] ,])"

-- Andrew Grumet, March 7, 2000

Also you can avoid this kind of 'deadlock' by specifying an 'on delete' clause in your family_relatives table:

create table family_relatives (

relative_id integer primary key,
-- optional pointer to users table
user_id references users,
-- note the 'on delete' clause
spouse references family_relatives on delete set null,
mother references family_relatives,
father references family_relatives,
-- in case they don't know the exact birthdate
birthyear integer,
birthday date,
-- sadly, not everyone is still with us
deathyear integer,
first_names varchar(100) not null,
last_name varchar(100) not null,
sex char(1) check (sex in ('m','f')),
-- note the use of multi-column check constraints
check ( birthyear is not null or birthday is not null)


);


-- Daniel Tio, May 20, 2000

I just don't actually delete anyone, I just set deleted_date = sysdate. Then, in my family_relatives_display_view, I use a where deleted_date is null. This way, when my Aunt Jo Anne accidently deletes Uncle Doug, it's a little easier to fix. And I don't have to deal with invalidating references.

-- Ryan Campbell, July 15, 2000