Am I the last man standing, faced with writing stockmanagement systems then? ;)

Many thanks for your thoughts.




>>Assuming a StockItems Table and a Transactions Table. If I want the Transactions Table to have a Foreign Key into the StockItems, and the StockItems to have a foreign keys into Transactions, there's no way I will be able to add a record in both tables (some Transactions will add StockItems) during the same transaction if I make both Foreign Keys non nullable.
>>
>>a) Is there?
>>b) Is it ok the set foreign keys nullable?
>>c) Is it not best practice to cross reference like this?
>>d) is there anything else wrong in my reasoning?
>
>Actually I think you should be able to automatically create a parcel from an insert trigger when you have a transaction which creates it (i.e. purchase). Not that I've tried it, but somehow sounds possible :).
>
>There'd be an awkward dance, when you're creating two records with automatic keys, which should need to be created in the same transaction, then PK of each inserted as a FK into the other.
>
>Other than that, if you intend to create these records independently, you'll have to write even more code and would have to wrap it all in a transaction, or else you may get dragged into transactions which created new parcel which doesn't exist, or have parcels created from nonexistent transactions - which will be returned as an error because your constraints prevent that.
>
>If that doesn't work, you may have a record with -1 as a PK in both tables, and use that as a temporary FK so at least you don't get clobbered by your RI rules. Or have it your way and allow nulls... You are aware that you're talking to perhaps few dozen people and the chances that anyone has actually tried what you ask are small, no?