Updating/Deleteing Objects With One-To-Many Relationships

[Seth Green <se...@gmail.com>]

I've spent a fair amount of time reading documentation and googling,  
and while I have found information on selecting objects with one-to- 
many relationships, I have yet to find anything on updating and  
deleting.

So, if I have

class Person {
	int id;
	String name;
	List<Car> cars;
}

class Person {
	int id;
	String name;
}

And I have 3 tables

person (
	id,
	name
)

car (
	id,
	name
)

person_x_car (
	person_id
	car_id
)

How is it that I go about

1) Updating a Person who has changes to their cars
2) Deleting a Person and subsequently deleting the relevant rows in  
person_x_car

Thanks

Re: Updating/Deleteing Objects With One-To-Many Relationships

[Seth Green <se...@gmail.com>]

The second class was meant to be Car. Sorry for the typo.

Thanks for the info, that is exactly what I wanted to know.

On Feb 25, 2009, at 3:14 AM, Ingmar Lötzsch <iloetzsch@asci-systemhaus.d 
e> wrote:

>> So, if I have
>>
>> class Person {
>>    int id;
>>    String name;
>>    List<Car> cars;
>> }
>>
>> class Person {
>>    int id;
>>    String name;
>> }
>
> Have you really two versions of class Person?
>
>> And I have 3 tables
>>
>> person (
>>    id,
>>    name
>> )
>>
>> car (
>>    id,
>>    name
>> )
>>
>> person_x_car (
>>    person_id
>>    car_id
>> )
>>
>> How is it that I go about
>>
>> 1) Updating a Person who has changes to their cars
>
> There is no UPDATE statement to achieve this. You can DELETE all  
> rows in
> person_x_car and then INSERT the recent pairs (person_id, car_id), if
> there are no dependencies on the table person_x_car. Otherwise, or in
> case you want to log the changes, you have to identify the disjoined
> respectively added cars and DELETE/INSERT the appropriate rows.
>
>> 2) Deleting a Person and subsequently deleting the relevant rows in
>> person_x_car
>
> If your DBMS support DELETE ON CASCADE you can just delete the  
> person row.
>
> -- ALTER TABLE person_x_car DROP CONSTRAINT fk_person_x_car_person;
> ALTER TABLE person_x_car
> ADD CONSTRAINT fk_person_x_car_person FOREIGN KEY (person_id)
> REFERENCES person (id) ON DELETE CASCADE;
>
> Otherwise you have to iterate over the car list and delete each join
> before deleting the person.

Re: Updating/Deleteing Objects With One-To-Many Relationships

[Ingmar Lötzsch <il...@asci-systemhaus.de>]

> So, if I have
> 
> class Person {
>     int id;
>     String name;
>     List<Car> cars;
> }
> 
> class Person {
>     int id;
>     String name;
> }

Have you really two versions of class Person?

> And I have 3 tables
> 
> person (
>     id,
>     name
> )
> 
> car (
>     id,
>     name
> )
> 
> person_x_car (
>     person_id
>     car_id
> )
> 
> How is it that I go about
> 
> 1) Updating a Person who has changes to their cars

There is no UPDATE statement to achieve this. You can DELETE all rows in
person_x_car and then INSERT the recent pairs (person_id, car_id), if
there are no dependencies on the table person_x_car. Otherwise, or in
case you want to log the changes, you have to identify the disjoined
respectively added cars and DELETE/INSERT the appropriate rows.

> 2) Deleting a Person and subsequently deleting the relevant rows in
> person_x_car

If your DBMS support DELETE ON CASCADE you can just delete the person row.

-- ALTER TABLE person_x_car DROP CONSTRAINT fk_person_x_car_person;
ALTER TABLE person_x_car
ADD CONSTRAINT fk_person_x_car_person FOREIGN KEY (person_id)
REFERENCES person (id) ON DELETE CASCADE;

Otherwise you have to iterate over the car list and delete each join
before deleting the person.