Sorun: Benim veritabanında otomatik artan birincil anahtarı kullandığınızda, bu her zaman olur:
Ben 10 Öğeler ile Sipariş saklamak istiyorum. Sipariş Öğeler Sipariş aittir. Yani, sipariş saklamak (doğru?, Eşzamanlılık söz konusu olduğunda tehlikeli) son eklenen id için veritabanı isteyin ve sonra yabancı anahtar (order_id) ile 10 Öğeler saklayın.
Yani her zaman yapmak zorunda:
INSERT ...
last_inserted_id = db.lastInsertId ();
INSERT ... INSERT ... INSERT ...
ve bu ben bir yabancı anahtar gereken hemen hemen tüm vakalarda INSERT işlemleri kullanarak engelleyen inanıyorum.
Onlar gerçekten iyi iseniz Yani ... burada bazı çözümler, ve ben bilmiyorum:
A) Don't use auto_increment keys! Use a key table?
Key Table would have two fields: table_name, next_key. Every time I need a key for a table to insert a new dataset, first I ask for the next_key by accessing a special static KeyGenerator class method. This does a SELECT and an UPDATE, if possible in one transaction (would that work?). Of course I would request that for every affected table. Next, I can INSERT my entire object graph in one transaction without playing ping-pong with the database, before I know the keys already in advance.
B) Using GUUID / UUID algorithm for keys? These suppose to be really unique worldwide, and they're LARGE. I mean ... L_A_R_G_E. So a big amount of memory would go into these gigantic keys. Indexing will be hard, right? And data retrieval will be a pain for the database - at least I guess - integer keys are much faster to handle. On the other hand, these also provide some security: Visitors can't iterate anymore over all orders or all users or all pictures by just incrementing the id parameter.
C) Stick with auto_incremented keys? Ok, if then, what about transactions like described in the example above? How can I solve that? Maybe by inserting a Ghost Row first and then doing an transaction with one UPDATE + n INSERTs?
D) What else?
