I have a main table with an autonumber key. I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several records in the new table). If the new table links to the main table via that autonumber key, then I (or the user) has to supply the corresponding number for each new record in the new table. Is there a way to automate any aspect of this, so the user doesn't have to keep cross-checking this number when he makes a new entry?
Autonumber and one-to-many relationship
auto-incrementrelational-theorytable
Related Question
- Mysql – one to one relationship
- Database design: Normalizing a “(many-to-many)-to-many” relationship
- Redesigning a table that has a lot of columns (not always related)
- Many-to-many relationship when one entity is without attributes
- SQL – Many-to-Many relationship with same table and with relationship constraint
- How to handle variations of the same data point
- MySQL – Set trigger BEFORE INSERT for new ID is safe
Best Answer
Most brands provide methods to retrieve the key an insert created in an identity or auto_increment column.
Unfortunately there is no general method for this, it will depend on the brand.
For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).