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.
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
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
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-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.
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
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.
2006/2/5, Johan Van M. [email protected]:
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
2006/2/5, Johan Van M. [email protected]:
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
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