2

I am trying to understand relationships fully, and have gone through alot of tutorials. Still i linger on a few things (using MySQLWORKBENCH): enter image description here 1. Every user can upload a product 2. Every product can have multiple categories, bids, ratings, etc 3. Every user can have multiple phonenumbers (there are more, but this is the basic setup)

Is this correct?: 1 - I used a 1:n relationship, since every user can upload multiple products. 2 and 3. I used n:m relationship, since there can be multiple products with multiple categories, bids, ratings, etc.

DELETE/UPDATE: I used ON UPDATE CASCADE and ON DELETE CASCADE everywhere where there is a foreign key...that being 'product', 'category_tags_has_products', 'bid_price_has_product', 'phone_has_user'.

I tried to delete a product like this (php): mysql_query("DELETE FROM product WHERE id='$id'"); I get a 1054 error, which is a foreign key error. What are the best practises here? It is to my understanding that i shouldn't need to do deletions in any other than the parent-table?

Thanx!

000
  • 26,951
  • 10
  • 71
  • 101
user1163859
  • 123
  • 2
  • 14

2 Answers2

1

The relations look correct. To find out what is wrong with your query check the $id variable. Or check the whole query and then run it in the console/phpMyAdmin/etc:

$query = "DELETE FROM product WHERE id='$id'";
var_dump($query);
mysql_query($query);

P.S.: and don't forget to escape all data got from the users! Like this:

$id = mysql_real_escape_string($_GET['id']);
Minras
  • 4,136
  • 4
  • 18
  • 18
  • Thanks. But, this still doesn't work. I tried deleting using phpmyadmin, but i get this error: – user1163859 Feb 21 '12 at 14:22
  • #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`databasename`.`category_tags_has_product`, CONSTRAINT `fk_category_tags_has_product_product1` FOREIGN KEY (`product_id`, `product_user_id`) REFERENCES `product` (`id`, `user_id`) ON DELETE NO AC) – user1163859 Feb 21 '12 at 14:25
  • Put `ON UPDATE CASCADE` and `ON DELETE CASCADE` on `category_tags_has_product.product_id`. Currently you seem to have no action for `ON DELETE` and when you are trying to delete the product from the `product` table, the corresponding records stays in the `category_tags_has_product` table and you get error, because the record in `category_tags_has_product` tries to point to deleted row in the `product` table. – Minras Feb 21 '12 at 15:45
1

You have a lot of identifying relationships, which mean that the foreign key form part of the primary key on the second table. This is not necessary in most instances, and is only really useful in instances such as link tables.

To this end I would change the user->product link to be non-identifying, which will make user_id a Foreign Key instead of being part of the Primary Key. In workbench the shortcut for a 1:n non-identifying relationship is key '2' (where as '4' is identifying). This in turn should remove the user_id fields from the tables which product links onto.

When you delete a product, it should cascade to the 3 link tables that it links to. At present it may be that it is trying to delete from users also depending on how the FK is set up. The following should cascade deletions (assuming a deletion is permanent and you just want to clear out all linked records)

DELETE FROM product -> deletes from any table with product_id in DELETE FROM user -> deletes from any table with user_id in

The same applies for phone, rating, bid_price, category_tags.

I hope this if of use, if you need any further pointers feel free to shout :)

Simon at The Access Group
  • 6,932
  • 22
  • 45
  • Changed user->product link to non-identifying. – user1163859 Feb 21 '12 at 14:42
  • I am deleting like this: $query = "DELETE FROM product WHERE id='$id'"; var_dump($query); mysql_query($query); – user1163859 Feb 21 '12 at 14:43
  • I tried deleting directly in phpmyadmin, and it gives me the same error. I randomly delete the record in category_tags_has_product, and then i was able to delete the product! strange. any ideas what i'm doing wrong? – user1163859 Feb 21 '12 at 14:46
  • Can you update the schema diagram attached to the question? Without the identifying link `user_id` should be out the product PK, and `product_user_id` should no longer be a field in `category_tags_has_products`, `rating_has_products`, and `bid_price_has_product`. In theory it should function now, else will need further information. Also you can use `"DELETE FROM product WHERE id = $id"`, because id is numeric you don't need to quote it, quotes are for strings. – Simon at The Access Group Feb 21 '12 at 14:46
  • You need to remove the product_user_id PK fields from the link tables still - Workbench isn't good at cleaning up after itself. Simplest to delete the links + remake them now that the products table is correct + then sync structure changes to the database. – Simon at The Access Group Feb 21 '12 at 14:55
  • Updated the diagram. This is really frustrating...cleaned the tables, but still i get the same 1451 error :/ – user1163859 Feb 21 '12 at 22:46