MySQL Workbench and Primary Keys

[Banana Man]

Hi,

I am using MySQL workbench for the first time. I have created an InnoDB relational database. When i connect two tables with a many to one connection it automatically adds a new row in the many table that references the row_id in the other table. When it adds this new row it inserts it as a Primary row. So now i have a table with 2 primary rows. Is this how things should be setup? I though every table should only have one primary row? Should i set the newly added relational row so that it's not a primary row?

On a side note. I am using phpMyAdmin through MAMP. I have read some tutorials about connecting MySQL workbench to MAMP's phpMyAdmin. In the tutorials it states that i should configure MySQL workbench to use the file located at /Applications/MAMP/tmp/mysql/mysql.sock but when i go to this location their is no file with this name (http://phpprotip.com/2011/10/using-m...nch-with-mamp/). Anyone know where i can find this file?

Thanks!

[r937]

2 primary rows??? perhaps you meant column?

please do a SHOW CREATE TABLE for each table, so we can see what the workbench created

[Banana Man]

Your right about the columns. Here's an example of a table create statement. It is declaring 3 columns as being primary (`row_id`, `mytable_farmers_row_id`, `mytable_farmers_mytable_users_row_id`):

-- -----------------------------------------------------
-- Table `db1204346_mytable`.`mytable_farm_open_days`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db1204346_mytable`.`mytable_farm_open_days` (
`row_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`mytable_farmers_row_id` INT(11) UNSIGNED NOT NULL ,
`mytable_farmers_mytable_users_row_id` INT(11) UNSIGNED NOT NULL ,
`open_day_title` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
`open_day_description` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
`open_day_start_date` DATE NOT NULL ,
`open_day_start_time` TIME NOT NULL ,
`open_day_finish_time` TIME NOT NULL ,
PRIMARY KEY (`row_id`, `mytable_farmers_row_id`, `mytable_farmers_mytable_users_row_id`) ,
INDEX `fk_farm_open_days_mytable_farmers1_idx` (`mytable_farmers_row_id` ASC, `mytable_farmers_mytable_users_row_id` ASC) ,
CONSTRAINT `fk_farm_open_days_mytable_farmers1`
FOREIGN KEY (`mytable_farmers_row_id` )
REFERENCES `db1204346_mytable`.`mytable_farmers` (`row_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 85
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

[r937]

looks broken to me

in mysql, an auto_increment must be (part of) the primary -- but it doesn't make sense to have other columns included as well

perhaps there is something about how you're relating tables that makes workbench do this (i dunno, i'm not a workbench user)

hopefully there's an easy way to fix it

[felgall]

Looks like whoever created that product believes in generating unnecessary autoincrement keys for everything. The second and third fields in that key should be all that is actually required. It is quite common for a table that has a many to one relationship to another table to require one more column in the primary key than the table it relates to.

[K. Wolfe]

I can see using an AI ID, but not using that AI as part of the primary key.

PRIMARY KEY (`mytable_farmers_row_id`, `mytable_farmers_mytable_users_row_id`) ,

And leaving the AI row along makes sense to me to restrict 1 of each... what ever that is... and then be able to use row_id as the row pointer, though not completely necessary..

I agree withe everyone else though, this is just broke, starting with the naming convention.

An example of why you might use a non primary key'd AI:

tbl_users
-------
user_id AI, PK
user_name

tbl_user_roles
-------
role_id AI
user_id PK
role_name PK

This would ensure only one role per user is allowed in, and I could perhaps use the role_id to delete certain rows, but should really be handled though dual criteria on the PKs.

[felgall]

[QUOTE=K. Wolfe;5251726]This would ensure only one role per user is allowed in/QUOTE]

If only one role per user is allowed then it would make more sense for the role to be a field in the user table and not a separate table.

If you properly normalize your data you shouldn't end up with any one to one tables - of course if it were a huge field and the relationship were one to zero or one then it would be a different matter but even if only a small percentage have a role(assuming a role can fit in a VARCHAR) then having a field for it in the user table would be reasonable.

[r937]

If you properly normalize your data you shouldn't end up with any one to one tables

not true

normalization rules do not, if you will excuse the pun, rule out one-to-one tables

you might find one-to-one tables unattractive for whatever reasons, but they do conform to normalization

and by the way, there really is no such thing as strictly one-to-one, it's always one-to-zero-or-one

[K. Wolfe]

This would ensure only one role per user is allowed in

If only one role per user is allowed then it would make more sense for the role to be a field in the user table and not a separate table.

If you properly normalize your data you shouldn't end up with any one to one tables - of course if it were a huge field and the relationship were one to zero or one then it would be a different matter but even if only a small percentage have a role(assuming a role can fit in a VARCHAR) then having a field for it in the user table would be reasonable.

That was also a one to many example provided. Each user may have more than one UNIQUE role.

USER_ID ROLE
------- ------
45 Reporting
45 Publishing

[Banana Man]

Thanks for the input! I think i need to start from scratch and first do a bit of reading up on MySQL relational database naming conventions.

[r937]

...do a bit of reading up on MySQL relational database naming conventions.

good luck

you will find conflicting conventions, some making more sense than others

[felgall]

not true

normalization rules do not, if you will excuse the pun, rule out one-to-one tables

you might find one-to-one tables unattractive for whatever reasons, but they do conform to normalization

and by the way, there really is no such thing as strictly one-to-one, it's always one-to-zero-or-one

If you had read my entire post you would have seen that the next paragraph covered one-to-zero-or-one as being a different situation to strictly one-to-one.

Perhaps the official normalization rules don't rule out strict one-to-one but if they don't then it is because the rule is too obvious to need stating - otherwise you could legitimately have lots of tableswith only one field that isn't a part of the primary key where each field apart from the primary key has its own table that associateds that field with the key - you'd then need to join all the tables together in order to extract all the one to one data.

Strict one-to-one relationships should involve placing the fields in the same table regardless of whether or not the written normalization rules require it as not doing so would rarely provide any benefit.

As I said before - with one-to-zero-or-one it would depend on how often the field has a value and how big the field needs to be as to whether or not the same table should be used - if the normalization rules don't specify one way or the other then the conversion of the logical design to a physical design should take care of it.

Also there is such a thing as strict one-to-one - for example if you have a width field and a height field then they will have a strict one-to-one relationship where there is nothing that can have a width that does not also have a height. Setting up two tables with the width in one and the height in the other would be silly.

[K. Wolfe]

Strict one-to-one relationships should involve placing the fields in the same table regardless of whether or not the written normalization rules require it as not doing so would rarely provide any benefit.

Tell that to a financial organization that stores literally thousands of 1-1 relational columns on customers. I would not be a happy camper.

[r937]

TL;DR

what i meant was, you cannot actually implement a strict one-to-one relationship in tables

declare the foreign keys to relate them, and all you will accomplish is that the child must have a parent (one-to-zero-or-one)

there's nothing in PK/FK syntax that ~requires~ a parent to have a child

go ahead, test it for yourself

as for that other stuff you wrote, i can't seem to digest your overly long sentences