Forum: Ruby on Rails Agile Rails book: depot problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A07ec64186c6d821ca51abb2df646dda?d=identicon&s=25 Johan Van mol (johan-vm)
on 2006-02-05 11:49
I recently finished the first part of the "Agile Rails" book.  But, the
depot doesn't completely work as it should.  I still have 2 problems:
I will explain them in 2 threads.

The first one is:
I can't destroy products (I can only creat and update them).  When I try
to delete a product --> http://localhost:3001/admin/destroy/3
I get this error message:

ActiveRecord::StatementInvalid in Admin#destroy
Mysql::Error: #23000Cannot delete or update a parent row: a foreign key
constraint fails (`depot_development/line_items`, CONSTRAINT
`fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products`
(`id`)):             DELETE FROM products
            WHERE id = 3

Can someone explain me what's going wrong?  I'm not a MySQL expert...
Before I started to use Rails, I didn't even know what a foreign key
was...

Thanks!

Johan.
Ad91bce9c9a8c68b6942607e4721f74e?d=identicon&s=25 Beate Paland (Guest)
on 2006-02-05 14:10
(Received via mailing list)
> ActiveRecord::StatementInvalid in Admin#destroy
> Mysql::Error: #23000Cannot delete or update a parent row: a foreign key
> constraint fails (`depot_development/line_items`, CONSTRAINT
> `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products`
> (`id`)):             DELETE FROM products
>             WHERE id = 3

How does your product Model look like? Is there a relation to
line_items?

Beate
A07ec64186c6d821ca51abb2df646dda?d=identicon&s=25 Johan Van mol (johan-vm)
on 2006-02-05 14:44
My product Model doesn't have a relation.

However, my line_item Model does have these relations:

>belongs_to :product
>belongs_to :order

So, I thought I should add

>has_many :line_items

to my product Model, but I didn't work... I still get:

>ActiveRecord::StatementInvalid in Admin#destroy
>Mysql::Error: #23000Cannot delete or update a parent row:
>a foreign key constraint fails (`depot_development/line_items`,
>CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`)
>REFERENCES `products` (`id`)): DELETE FROM >products WHERE id = 4
Ad91bce9c9a8c68b6942607e4721f74e?d=identicon&s=25 Beate Paland (Guest)
on 2006-02-05 15:13
(Received via mailing list)
2006/2/5, Johan Van Mol <johan.van.mol@gmail.com>:
> My product Model doesn't have a relation.
>
> However, my line_item Model does have these relations:
>
> >belongs_to :product
> >belongs_to :order
>
> So, I thought I should add
>
> >has_many :line_items

no, don't ;)
Who does your controller look like?

Beate
A07ec64186c6d821ca51abb2df646dda?d=identicon&s=25 Johan Van mol (johan-vm)
on 2006-02-05 15:27
Well, I did add it :-)
It didn't fix the problem,
but everything works now (with "has_many :line_items" in the product
model...)

My problem had to do with my MySQL line_items database.

Apparantly, I had to tell the database to CASCADE on delete.
With that done, everything works fine.

Here is where I found the solution:
http://dev.mysql.com/doc/refman/5.0/en/innodb-fore...

>CASCADE: Delete or update the row from the parent table and automatically delete >or 
update the matching rows in the child table. Both ON DELETE CASCADE and ON >UPDATE CASCADE 
are supported. Between two tables, you should not define several >ON UPDATE CASCADE 
clauses that act on the same column in the parent table or in >the child table.

Before, I used NO ACTION
>NO ACTION: In standard SQL, NO ACTION means no action in the sense that an >attempt to 
delete or update a primary key value is not allowed to proceed if >there is a related 
foreign key value in the referenced table. InnoDB rejects >the delete or update operation 
for the parent table.
Ad91bce9c9a8c68b6942607e4721f74e?d=identicon&s=25 Beate Paland (Guest)
on 2006-02-05 16:41
(Received via mailing list)
You could do this, of couse, but it would be better to say rails to do
this.
I also think that this is not the "state of art", or do you want to
delete orders, when you delete a product? I managed this by putting
the main infos of the product in line_items, i.e. articlenumber,
title, price. So I can delete products again.

I can give you an example: When I delete an order, the belonging
line_items should be removed. So this is what I have in my order
model:

  has_many :line_items, :dependent => true

This workes ;)

Beate
A07ec64186c6d821ca51abb2df646dda?d=identicon&s=25 Johan Van mol (johan-vm)
on 2006-02-05 19:17
Did you duplicate the price, title, ... then?
Do you have a products table with price, title,...
and a line_items table that doesn't reference the products table, but
stores the price, title,... by itself?
That way your DB tables don't rely on each other?
I guess that's a way to do it..

I agree that my code isn't state of the art...
However, I would like to use the delete cascade rule...
I think it makes sense to delete orders that rely on a product you can't
ship anymore.  But then, the code should be adapted, so you can notify
people who ordered such a product.

I have to say, this relational DB stuff isn't my favorite part of RoR.
I don't know enough about it yet.  Maybe I should read about MySQL.

Johan.
A07ec64186c6d821ca51abb2df646dda?d=identicon&s=25 Johan Van mol (johan-vm)
on 2006-02-05 19:24
> I agree that my code isn't state of the art...
> However, I would like to use the delete cascade rule...
> I think it makes sense to delete orders that rely on a product you can't
> ship anymore.  But then, the code should be adapted, so you can notify
> people who ordered such a product.

Thinking about it, this is a worst case scenario - and not very
professional.
The product running out of stock should automattically be deleted when
the last item has been sold, without deleting the pending order(s).
You're right Beate :-)
Ad91bce9c9a8c68b6942607e4721f74e?d=identicon&s=25 Beate Paland (Guest)
on 2006-02-05 20:19
(Received via mailing list)
2006/2/5, Johan Van Mol <johan.van.mol@gmail.com>:
> Did you duplicate the price, title, ... then?
> Do you have a products table with price, title,...
> and a line_items table that doesn't reference the products table, but
> stores the price, title,... by itself?

Yes, that's the way I do it.

> That way your DB tables don't rely on each other?
> I guess that's a way to do it..

yes, they don't rely on each other.

> I agree that my code isn't state of the art...
> However, I would like to use the delete cascade rule...
> I think it makes sense to delete orders that rely on a product you can't
> ship anymore.  But then, the code should be adapted, so you can notify
> people who ordered such a product.

If a product is deleted, it won't be displayed in the shop afterwards,
and so it can't be ordered. At least in my shop ;)

> I have to say, this relational DB stuff isn't my favorite part of RoR.
> I don't know enough about it yet.  Maybe I should read about MySQL.

You need not to know this much SQL for Rails, but you should
understand the ralations: 1:n, 1:1, n:m and how you build them in
tables. Then you write your relations in the models, and thats all ;)

Beate
This topic is locked and can not be replied to.