Foreign keys dillema

[PpSi]

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

+-----------+------------+-- 
| Id(int,pk)| user       |....etc
+-----------+------------+----- 
|   x       | user1      |
+-----------+----------- +----
|   y       | user2      |
+-----------+----------- +----
                .
                .
                .

userdata:

+-----------+---------+-----------+ 
| 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

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

[traq]

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:

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

[PpSi]

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 ! ^^

[traq]

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].

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,

[PpSi]

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!

[traq]

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.