Getting started with foreign keys

[prognoob]

I have two tables...
both of them have first field as autoincremented numeric key
and second field as user id of the person...
i want to make sure when data is inserted into row 1 of table1, the relevant data also gets inserted in row 1 of table2. In other words, I want to make sure that if two rows of these two tables have the same primary key (that autoincrement int), then they both are pointing to information of the same person...

i looked into foreign key... I could not figure out a way to achieve what I want to using foreign key...

[Atli]

Hi.

You are looking in the right place all right. What you need to do is create a FOREIGN KEY constraint between the primary key of table1 to the foreign key in table2

For example:
[code=mysql]
CREATE TABLE table1 (
UserID SERIAL Primary Key,
UserName VARCHAR(255) NOT NULL
/* And so on */
)

CREATE TABLE table2 (
ContactID SERIAL Primary Key,
ContactTitle VARCHAR(255) NOT NULL Default 'email',
ContactValue VARCHAR(255) NOT NULL,
UserID_FK BigInt NOT NULL REFERENCES table1(UserID)
)
[/code]
See the last line in table2. I simply put REFERENCES tblName(colName ) to link it to the column in table1.
There are also more complicated ways of doing this, for example the FOREIGN KEY syntax, but this will also work.

[Atli]

After looking through your post again I see that I may have misunderstood you.

Are you trying to synchronize the Primary Keys of several tables?

[prognoob]

yeah your first post does not help me quite much...
but yeah you second post probably indicates what I am trying to do
thanks

[Atli]

yeah your first post does not help me quite much...
but yeah you second post probably indicates what I am trying to do
thanks

I would recommend against that. Try to use the Primary Key -> Foreign Key scheme instead, as that much much more flexible and easier to set up.
You could simply make one of the tables a parent table and reference the Primary Key of that table as a Foreign key in the other once.

Linking the primary keys of the tables would have to involve some external API to work properly.

[prognoob]

no, i think i still did not make my question clear...

all I am saying is ...

table1 made user1 get numeric key 1, how do I ensure table 2 will make user1 get numeric key 1 too.
when i do the foreign key, it doesnt quite ensure that...
did I make my question clear?

[Atli]

no, i think i still did not make my question clear...

all I am saying is ...

table1 made user1 get numeric key 1, how do I ensure table 2 will make user1 get numeric key 1 too.
when i do the foreign key, it doesnt quite ensure that...
did I make my question clear?

Not really no. I don't understand what you are trying to accomplish.
It sounds to me like you just want some rows in some tables to be linked to a row in your main table, like I did in my first post.

Could you give an example? Maybe some sample data?

[pbmods]

Changed thread title to better describe the problem.