UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> MySql    
 
   
zocker
post May 6 2009, 10:48 AM
Post #1

Utterly Eccentric and Moderator
Posts: 3,806
From: Bristol / Ipswich / Spain



What are ..or are the diffences between Identifying and Non Identifying relationships in MySql please?
hank
Zocker
Go to the top of the page
 
+
zocker
post May 6 2009, 11:39 AM
Post #2

Utterly Eccentric and Moderator
Posts: 3,806
From: Bristol / Ipswich / Spain



mm I got it its like acces referential integrity...innit?
Go to the top of the page
 
+
BananaRepublic
post May 6 2009, 12:00 PM
Post #3

Admin under the bridge
Posts: 1,197
From: Banana Republic



FWIW, I don't believe MySQL (InnoDB) has such concept of "non-referential enforced relationship". I don't see the reference anywhere in the manual or the client; only the workbench. Is that where you got it?
When I tried it myself, it just added a new column to act as the key, which I assume is the purpose. Identifying relationship add a column with same name. The SQL syntax for creating foreign key are same; only the column is different.
I did that make sense?
Onormally just "Pick a column for reference".
Go to the top of the page
 
+
BananaRepublic
post May 6 2009, 12:06 PM
Post #4

Admin under the bridge
Posts: 1,197
From: Banana Republic



Aha, manual entry.
pparently I missed the fact that when I add a non-identifying relationship, a new column is added but it's non-key, while adding a identifying relationships add a key column.
Go to the top of the page
 
+
zocker
post May 6 2009, 01:37 PM
Post #5

Utterly Eccentric and Moderator
Posts: 3,806
From: Bristol / Ipswich / Spain



With workbench, there is a facility make ERR diagrams and there are several 'tools' to add relationships which is where I saw it....
This is complex!
Thanks
Z
BTW .....Scarecrow?
Go to the top of the page
 
+
zocker
post May 6 2009, 01:39 PM
Post #6

Utterly Eccentric and Moderator
Posts: 3,806
From: Bristol / Ipswich / Spain



I made a structure with three tables..looks good...how do you get data into it? Have I missed something big here?
errr whats (InnoDB)?
Thx!
Edited by: zocker on Wed May 6 14:44:38 EDT 2009.
Go to the top of the page
 
+
BananaRepublic
post May 6 2009, 02:12 PM
Post #7

Admin under the bridge
Posts: 1,197
From: Banana Republic



Zocker,

Indeed, you are right! It's scarecrow! <

Yes, I think workbench kind of overdid it with options to create relationships in five different ways; I've chosen to just define the columns myself and use the 5th option (pick a column to reference). However, the manner of how they create relationships are basically same; that is they all will add the syntax to the table defintion:

For a non-identifying relationship:

For a identifying relationship, the difference is that ckey is now a key as well:


The constraint definition remains same; identifying just makes it a primary key. (I should add that the second table definition wasn't great example since we now have a redundant primary key, but this may be more useful if it was a junction table for example.)


Regarding InnoDB: MySQL is different from other RDMBS in that you get to define a variety of storage engines. If you want to have relationships and foreign keys, you must use InnoDB for all tables participating in relationships; other does not support foreign key constraints, and are non transactional (putting performance ahead of integrity, which may be appropriate for certain scenarios where we can tolerate some data loss or inconsistency such as logging for example).

Usually, the default engine for MySQL is MyISAM, so if you executed the table definition above, then do this:

CODE
SHOW CREATE TABLE tk\G


you'll see that MySQL added this to the definition:
CODE
CREATE TABLE t (
nbsp;  tk INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   PRIMARY KEY (tk)
) ENGINE = MyISAM;


You can explicitly specify InnoDB by replacing MyISAM with InnoDB. Don't forget that it has to be outside the () of the CREATE TABLE. You also can change an existing table's engine by doing a
CODE
ALTER TABLE tk ENGINE = InnoDB;



Regarding inserts in Workbench- If you double click a table in view or explorer, and bring up its defintion, you should see "Insert" as one of its tab. Click that, and click Editor to start inserting.

I did that help?


PS Edited to use quotes instead of code and replaced a variable that doesn't trip the language filter.

Edited by: BananaRepublic on Wed May 6 15:16:47 EDT 2009.
Go to the top of the page
 
+
zocker
post May 6 2009, 02:25 PM
Post #8

Utterly Eccentric and Moderator
Posts: 3,806
From: Bristol / Ipswich / Spain



Thanks for that, I thought MysQl was an easy one! I'll think it all over!
hx
Z
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 23rd November 2014 - 03:23 AM