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. -- Posted via http://www.ruby-forum.com/.
> 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 = 3How does your product Model look like? Is there a relation to line_items? Beate
My product Model doesn''t have a relation. However, my line_item Model does have these relations:>belongs_to :product >belongs_to :orderSo, I thought I should add>has_many :line_itemsto 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-- Posted via http://www.ruby-forum.com/.
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_itemsno, don''t ;) Who does your controller look like? 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.-- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
> 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 :-) -- Posted via http://www.ruby-forum.com/.
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