Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
cclear

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.  

Share this post


Link to post
Share on other sites

Sorry, still new to IT, instructor just told me I dont' need foreign keys and that the figures just represent the relationship not that there is a foreign key, I'm making things harder than they really are.   <_<

Share this post


Link to post
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 =).

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...