SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL Workbench and Primary Keys

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    2 primary rows??? perhaps you meant column?

    please do a SHOW CREATE TABLE for each table, so we can see what the workbench created
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,832
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    I can see using an AI ID, but not using that AI as part of the primary key.

    Code:
    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.

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,832
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    [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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by felgall View Post
    Quote Originally Posted by K. Wolfe View Post
    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

  10. #10
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Banana Man View Post
    ...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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,832
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  13. #13
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by felgall View Post
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •