Denise Field Denise Field - 1 month ago 4x
SQL Question

SQL Database Parents/Children which one should I auto increment

I have two tables in an SQL Database


Underwriters can have many brokers and
Brokers can have many Underwriters

I created an intermediary Table called UnderwritersBrokers and I have an ID in that table that is linked via a one to many relation to each of the other tables.

I want to auto increment the record ID each time I enter a new record. which table would I set the auto increment on, the child tables or the parent(intermediary)

I apologise for being a novice, this is all new to me and I just cant get my head around it.

Thanks in advance for clearing this up.


You can use auto_increment in all three!

In particular, you want to use auto_increment in both the Underwriters and Brokers tables. This will give you, respectively, a unique UnderwriterId for each underwriter and a unique BrokerId for each broker.

The junction table should then use these ids. The junction table can also have its own id. I would add one there, but a unique id in a junction table is less important than in the entity tables.