C# Delete row from multiple tables

Question

Situation: I have 3 tables:

movies (pk:movietitle, movielength....etc)
rentals (pk:personid, fk:movietitle,...etc)
rentingpeople (pk+fk:personid, name, phone...etc)

On my form there is a listbox bindingsourced with the movie titles, next to the listbox there are textboxes bindingsourced from db.movies

When someone click on the rentthismovie button I would like to delete the current rental data about that movie from table rentals and rentingpeople.

I wrote the first part and get an error because of foreign keys problem (I mentioned primary key as pk and foreign key as fk in the tables above)

var search = (from g in db.Rentals 
              where g.Movietitle == (string)listBox1.SelectedValue select g).First();

db.Rentals.DeleteObject(search);
db.SaveChanges();

I get an error:

The DELETE statement conflicted with the REFERENCE constraint \"FK_Rentingpeople_Rentals\". The conflict occurred in database \"C:\USERS\PC\DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\FILMEK\FILMEK\BIN\DEBUG\DATABASE1.MDF\", table \"dbo.Rentingpeople\", column 'personid'.\r\nThe statement has been terminated.

Because of the primary-foreign key connection I must delete the data from rentingpeople table too as I read from this error but I can't really find a working solution.

Do you have any other table where you keep information of people that are "members" and can rent, other than your rentingpeople table? — Floremin
– Floremin, Commented Apr 22, 2014 at 15:19

Jason · Accepted Answer · 2014-04-22 15:23:20Z

2

The problem is in the db design

movies(pk:movietitle,movielength....etc)
rentals(pk:personid,fk:movietitle,...etc)
rentingpeople(pk+fk:personid,name,phone...etc)

If I got this right, movies contains the list of movies, rentingpeople is the list of people who are renting or have rented, and rentals tracks rentals. If so, rentingpeople.personid should be a pk, and rentals.personid should be an fk to the other, like this:

movies(pk:movietitle,movielength....etc)
rentals(fk:personid,fk:movietitle,...etc)
rentingpeople(pk:personid,name,phone...etc)

if you want to improve search on rentals (assuming any one person can exercise multiple rentals at the same time) you can introduce a non-unique index on personid, or a composite unique index to personid and movietitle on table rentals

answered Apr 22, 2014 at 15:23

Jason

3,9901 gold badge24 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3551399 Over a year ago

i changed now i get the error: Unable to update the EntitySet 'Rentals' because it has a DefiningQuery and no <DeleteFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

Jason Over a year ago

@user3551399 Are you using EF? I think it's a problem with ef that you need a pk in every table, if that's the case, put a PK in rentals table, for example you can make PK(personid, movietitle)

Matas Vaitkevicius · Accepted Answer · 2014-04-22 15:50:22Z

0

You need to delete all the PK and not null references to the object you are deleting before you delete object itself.
You can change non nullable columns to nullable ones if logic allows.

var rentalsToBeDeleted = db.Rentals.Where(o =>o.movieid == movieid).ToList();
for (int i = rentalsToBeDeleted.count; i < 0; i--)
{
 db.Rentals.DeleteObject(rentalsToBeDeleted.elementAt(i));
}

after all the referenced deleted.

db.SaveChanges();

writing without VS so mistakes are likely but you should get the idea.

edited Apr 22, 2014 at 15:50

answered Apr 22, 2014 at 15:14

Matas Vaitkevicius

61.8k37 gold badges253 silver badges278 bronze badges

6 Comments

user3551399 Over a year ago

everything allows nulls but primary and foreign keys. I can't find the way to delete the personid from rentingpeople which is connected to the rentals personid who rented the selected movie

user3551399 Over a year ago

unfortunately elementsat and delete : does not contain definition and no extension method...

Matas Vaitkevicius Over a year ago

add to list at the end of db.Rentals.Where(o =>o.movieid == movieid).ToList();

user3551399 Over a year ago

still.. :( anyway i changed to o.movieid==listbox1.selectedvalu or it is underlined too with red and i need to search for the selectedmovie

user3551399 Over a year ago

hm the code clean now,but nothing deleted :( now i have 3 same movie titles in the rentals table,none of them gets deleted

|

Collectives™ on Stack Overflow

C# Delete row from multiple tables

2 Answers 2

2 Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Related