foreign key issues

[devilsvein]

Started to use foreign keys for the first time and have come across a problem

 

I have a table called A_table in which I have a column there which auto-increments named P_ID.

 

I have another table called B_table and have a column called P_ID and i believe I have also set this to foreign key and referenced it to that in A_table.

 

(p_id is the row number)

 

i then have two queries in a page of signup of mine

 

mysqli_query($mysqli, "INSERT INTO A_table (Username, Password, Email, Gender, ip) VALUES ('$euser', '$hash', '$eemail', '$gender', '$ip')");
mysqli_query($mysqli, "INSERT INTO B_table (Energy, Shooting, concentration, brave, health) VALUES (100, 100, 100, 5, 100)");

 

The foreign key is my B_table doesnt have the same id of that in my A_table? It starts at 0, and the query executes and inserts fine

[Jessica]

What??

 

You need to insert a value into the column when you do your query. 

http://php.net/manual/en/mysqli.insert-id.php

 

Also if this is a 1-1 relationship you might as well put them in the same table. *shrug*.

[devilsvein]

Ok i see, i think its best that i keep to one long table. I thought you could break down data this way into smaller tables.

[Jessica]

You can, and you need to if it's a 1-many/0-many etc. But the data you're describing might as well be in one table, it will save you a lot of effort. If you are going to allow every user to have more than one character, they need to be separate. If one user = one character, save the bother and store them in one.

 

[Barand]

There are valid cases for having two tables with a one-to-one relationship.

 

Consider a employee table. It makes sense to put personal details (salary, tax etc) in one table and public stuff (name, job title, extension number etc) in another. That way permissions to access the two sets of data can be different.

[Jessica]

Totally agree.