Member Avatar for M40

Hi.

I have tables like these:

table1 (table1_id, table1_attribute1, table1_attribute2)
table2 (table2_id, table1_id*, table2_attribute1, table2_attribute2)

The primary key of the first table is table1_id. It is auto incrementing and it is also a foreign key for the other table - table2.

Let's say I want to add a new record in both tables.

To add a new record to table1 is simple. I don't have to give value to table1_id since it is auto incrementing.

The problem is with table2. Here I have to set a value for the foreign key table1_id. How can I get the auto incremented value of table1_id from table1 so I can use it in table2?

Do I make any sense?

Any help would be fantastic.

  • 2 Contributors
  • 4 Replies
  • 137 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by M40
Member Avatar for peter_budo 
select max(table1_id) from table1
Member Avatar for M40

Thank you very much peter_budo.

One more question. What if the database is used by a lot of people and it happen that 2 or more users whant to insert a record in the tables at the same time. Is it possible that something go wrong. For examle:

One user sends a query to get the max value of table1_id - select max(table1_id) from table1. Let's say the value is 10 000. Then this user sends a query to insert a record for this value of table1_id (10 000) in table2.
I suppose that the server require some time to process the query.
What if during that time another user send a query to get the max value of table1_id. Won't this user receive the same value of 10 000? This would cause the both users to try to add a record for the same value of table1_id in table2.
Do you think this is possible?

Member Avatar for peter_budo

I never heard that something like that happens, as long you designed your database properly it will never ocure. The primary key is unique for only one user or item

Member Avatar for M40

Ok. Thank you very much again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.