What are ..or are the diffences between Identifying and Non Identifying relationships in MySql please?
Thank
Zocker
Full Version: MySql
mm I got it its like acces referential integrity...innit?
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.
Did that make sense?
I normally just "Pick a column for reference".
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.
Did that make sense?
I normally just "Pick a column for reference".
Aha, manual entry.
Apparently 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.
Apparently 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.
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?
This is complex!
Thanks
Z
BTW .....Scarecrow?
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.
errr whats (InnoDB)?
Thx!
Edited by: zocker on Wed May 6 14:44:38 EDT 2009.
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:
you'll see that MySQL added this to the definition:
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
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.
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.
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:
QUOTE
CREATE TABLE t (
tk INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (tk)
);
CREATE TABLE c (
ckey INT NOT NULL AUTO_INCREMENT,
tk INT,
PRIMARY KEY (ckey),
CONSTRAINT FOREIGN KEY (tk) REFERENCES t (tk)
);
tk INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (tk)
);
CREATE TABLE c (
ckey INT NOT NULL AUTO_INCREMENT,
tk INT,
PRIMARY KEY (ckey),
CONSTRAINT FOREIGN KEY (tk) REFERENCES t (tk)
);
For a identifying relationship, the difference is that ckey is now a key as well:
QUOTE
CREATE TABLE c (
ckey INT NOT NULL AUTO_INCREMENT,
tk INT NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (ckey, tk),
CONSTRAINT FOREIGN KEY (tk) REFERENCES t (tk)
);
ckey INT NOT NULL AUTO_INCREMENT,
tk INT NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (ckey, tk),
CONSTRAINT FOREIGN KEY (tk) REFERENCES t (tk)
);
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 (
tk INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (tk)
) ENGINE = MyISAM;
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.
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.
Thanks for that, I thought MysQl was an easy one! I'll think it all over!
Thx
Z
Thx
Z
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.