Agile Rails book: depot problem


#1

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.


#2

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


#3

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


#4

Well, I did add it :slight_smile:
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-foreign-key-constraints.html

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.


#5

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 :wink:

Beate


#6

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.


#7

2006/2/5, Johan Van M. removed_email_address@domain.invalid:

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 :wink:
Who does your controller look like?

Beate


#8

2006/2/5, Johan Van M. removed_email_address@domain.invalid:

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 :wink:

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 :wink:

Beate


#9

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 :slight_smile: