Jump to content
Larry Ullman's Book Forums

Ch. 7 Error: Relation Could Not Be Added.


Recommended Posts

Ok, so I imported the SQL code for the second site. Now im in the designer view so I can physically see everything. When I start to create the foreign key links it goes well until i try to link "non_coffee_products" to "sales". I successfully linked "specific_coffees" to "sales" and after I did I still could not link "non_coffee_products" to "sales", but this time I get Error: Relation already exists. So my questions are 1. Why wont the foreign key work between "non-coffee-products" and "sales"? 2. How do I link them both to "sales" if only one relation can be set up? Thank you for looking into my inquiry.  

Link to comment
Share on other sites

Hello. Your instructor is right. relationships are not needed between tables. However, you should do them since mysql is after all a Relational database management system.

 

The foreign key relationships ensures referential integrity, which means, that you are making sure that the data in the child table is directly mapped to an existing value on the parent table. If this "referential integrity" between tables is not maintain then some garbage values can be imputed.

 

If you do not build the relationship, you or malicious users, could enter a product id in the sales tables even if the item does not really exist in your products table. Which can create a lot of garbage values.

 

If mysql does not let you create relationships it might be because:

1) the type of the data (int, varchar, etc) in the fields used for the primary/foreign key relationship are not the same. This happens because you cannot say that the value on an id (which is numeric usually) will map to a string value.

 

2) A foreign key on other tables or fields with the same name already exists (keys are objects. Just like tables, views, and others, you cannot have more than 1 of the same name).

 

3)The relationship in fact already exists.

 

If i forget any please someone quote and add =).

 

Hope this helps =). I also recommend reading about database normalization and being familiar with at least the 1st, 2nd, and 3rd normal forms.

 

Sorry for the long POST =).

Link to comment
Share on other sites

 Share

×
×
  • Create New...