Results 1 to 6 of 6

Thread: Foreign keys dillema

  1. #1
    Join Date
    Sep 2011
    Posts
    16
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Default Foreign keys dillema

    I'm currently working on a college project and I'm having a bit of trouble figuring my db structure out.

    I have a table "users" that stores the user account data(username, password, email). Each user would have several tables associated with it, and also, data rows in various other tables.

    Since each user could have several tables associated with it, i want to store the name of the tables and the name of a user, in a separate table.

    I have something like:

    users
    Code:
    +-----------+------------+-- 
    | Id(int,pk)| user       |....etc
    +-----------+------------+----- 
    |   x       | user1      |
    +-----------+----------- +----
    |   y       | user2      |
    +-----------+----------- +----
                    .
                    .
                    .
    userdata:
    Code:
    +-----------+---------+-----------+ 
    | Id(int,pk)| userid  |tablename  |....etc
    +-----------+---------+-----------+ 
    |   1       |     x   |   table1  |
    +-----------+---------+-----------+
    |   2       |     x   |   table2  |
    +-----------+---------+-----------+
    |   3       |     x   |   table3  |
    +-----------+---------+-----------+
    |   4       |     x   |   table4  |
    +-----------+---------+-----------+
                            .
                            .
                            .
    However, when I try to insert values like this in my usersdata table, I get
    Code:
    ERROR 1062: Duplicate entry '0' for key 'PRIMARY'
    
    SQL Statement:
    
    INSERT INTO `projdb`.`userdata` (`userid`, `tablename`) VALUES ('15', 'table2')
    
    
    
    ERROR 1062: Duplicate entry '0' for key 'PRIMARY'
    
    SQL Statement:
    
    INSERT INTO `projdb`.`userdata` (`userid`, `tablename`) VALUES ('15', 'table3')
    In my case, "x"=15. In "projdb", i have `userdata`.`userid` as a foreign key referencing `users`.`id` Both userid and id are INT, and `id` is the primary key wi auto increment in `users`. I have set ON UPDATE CASCADE, and ON DELETE CASCADE.

    Can I bypass this error? Or is my approach wrong?

    Any help would be gladly appreciated :<
    Last edited by PpSi; 12-17-2012 at 07:02 PM.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    ERROR 1062: Duplicate entry '0' for key 'PRIMARY'
    This would indicate that you did not set up your PRIMARY KEY as an AUTO_INCREMENT FIELD.
    You're not specifying a value to use as the PK, so MySQL is using the default value ( 0 ).

    Obviously, this will only work once.

    ------------
    As for your foreign keys design:

    ...Is there a reason you're associating *entire tables* to individual users? Is the table just for them? If (as I suspect) not, then you should be associating the record(s) to the user, not the table itself.


    This next part might not be relevant depending on your data structure, but most likely applies.

    A relations table (e.g., your userdata table) is good for many-to-many relationships. A network of friends is a good example of this type of relationship.

    However, where a relationship is many-to-one (the record is one of possibly several that are associated only with one user), it often makes more sense to store the foreign key with the "many" record itself. An example:

    Code:
    -- table `user`
    -- `id` is PK,AUTO_INCREMENT
     id | name | etc...
    ----+------+--------
     15 | john | ...
        .
        .
        .
    
    -- table `email`
    -- `email` is PK,UNIQUE
    -- `user_id` is FK,ON UPDATE CASCADE,ON DELETE CASCADE
     user_id | email
    ---------+---------------------------
     15      | john@example.com
    ---------+---------------------------
     15      | j.smith@other.example.com
    ---------+---------------------------
             .
             .
             .
    Viola!, associating "many" records (emails) to their "one" records (user) is done, quick and simple.
    Storing these relationships in a separate table would only complicate querying and slow down performance overall.

    If this doesn't address your problem, please explain your question further.
    Last edited by traq; 12-16-2012 at 07:34 PM.

  3. The Following User Says Thank You to traq For This Useful Post:

    PpSi (12-17-2012)

  4. #3
    Join Date
    Sep 2011
    Posts
    16
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Default

    Thanks, that was my mistake! "tableid" was not set as AUTO INCREMENT.

    We have to store each users's information on a separate table, this is my given problem specification. As I've seen, the next laboratory tackles records. I'm guessing I'll have to implement this same thing with records.

    Nevertheless, thank you for your time and for your given explanation. I never would have thought that was the problem.

    Thanks a lot ! ^^

  5. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    You're welcome; glad I could help.

    If your question has been answered, please mark your thread "resolved":
    • On your original post (post #1), click [edit], then click [go advanced].
    • In the "thread prefix" box, select "Resolved".
    • Click [save changes].


    Quote Originally Posted by PpSi View Post
    We have to store each users's information on a separate table, this is my given problem specification. As I've seen, the next laboratory tackles records. I'm guessing I'll have to implement this same thing with records.
    That still sounds "wrong" - unless each user has completely different types of info (seems unlikely), then it should all be in the same table. Storing similar info about different users in different tables is, quite simply, absurd. But perhaps there's something else here that I don't understand yet.

    Have fun,

  6. #5
    Join Date
    Sep 2011
    Posts
    16
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Default

    I guess our professor wants us to experiment with php-based sql commands& such. I guess it's just a step in his learning plan.

    I've made the requested changes,

    Thanks again Adrian!

  7. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    No prob. Personally, I think I'd be able to come up with *practical* examples that involved linking tables and JOIN queries, but I'm not the instructor, so we'll see what they have in store for you.

Similar Threads

  1. PHP/Mysql foreign key fill
    By evas in forum PHP
    Replies: 0
    Last Post: 06-01-2011, 07:01 PM
  2. Display value using a Foreign Key
    By ignitedintelligence in forum PHP
    Replies: 3
    Last Post: 06-23-2009, 06:56 AM
  3. How to make foreign key linking in PHP/MYSQL?
    By devil_vin in forum MySQL and other databases
    Replies: 1
    Last Post: 10-11-2007, 07:28 AM
  4. Replies: 2
    Last Post: 02-26-2007, 12:02 PM
  5. Max. Columns & Foreign Keys
    By real_estate in forum MySQL and other databases
    Replies: 1
    Last Post: 08-09-2006, 05:38 AM

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
  •