primary key as foreign key

[jimfog]

I have set a table in which the primary key(auto incremented) is also the foreign key, which references another primary key in the parent table. Despite I have made some insertion queries without problem I really do not know if the above logic is correct-problems may arise in the future, which I cannot predict now, cause of course I have not operated the db in other scenarios, unless of course-as already mentioned- insertion queries. I must say, that I intend referencing the specific table, with other child tables too-with foreign keys from there.

[justsomeguy]

It doesn't really make sense to have a foreign key that is also an auto-increment. You either want to use the next available value, or a value from another table. It doesn't really make sense to do both.

[jimfog]

Ok, I droped the primary key, but a question arises here:A question related to the way foreign keys work-I will get in a little more detail I have 2 tables, the parent has a column name CRid which is a primary key and the child table has a column named id which references CRid in the parent table(it is the foreign key in other words). The credentials(parent) table hold the username and the password of the user and the child table named end_users contains other related data, name, lastname...etc. I assume that when trying to insert to the end_users table the foreign_key column(id) takes automatically the values from CRid column in the parent table.As such, the query to insert to end_users is like this:

query("insert into end_users values('".$name."','".$lastname."')")

The last column is the foreign key column, which I expect to be filled automatically-the problem is it does not. I get the following message in the browser: Column count doesn't match value count at row 1. This happens of course because in the query above I do not put nothing in the 3rd column and that because I am assuming thedb will put there values-as this being the foreign key. Why this happens you think? I must note also that I write a second query too(not shown here)that I put data in the credentials table, namely the username and password.

[birbal]

foreign key does not insert data to automatically. what it does is check the data in child table and verify it against referenced parent table's column.

Edited November 21, 2012 by birbal

[jimfog]

hmmm...I really did not know that.Ok how am I going to proceed then? How Am i going to fill in the foreign key column? Am I going to select first the column in the parent table-the one in which the foreign key references to, and insert the corresponding valuesin the foreign key column in the child table? Meaning, a SELECT and an INSERT statement?

[justsomeguy]

If it's an autonumber column in the parent, then you can get the new ID natively. http://www.php.net/manual/en/mysqli.insert-id.php

[jimfog]

yes, I finally did it, using the insert_id method...to insert the Id(taken from the parent table) the in the child table.So I use 1 queries to add a row to the parent table and one row to the child table. And the question is if it is better to use the multi_query method when having to make more than one query, as is the case here. Does it worth to use this method? I just discovered it in the PHP manual

[justsomeguy]

It might save a couple milliseconds in connection overhead to send multiple queries at once.