locked
how to insert data into two table when two tables depend on each other? RRS feed

  • Question

  • User1038049161 posted
    suppose there are two table called tableA and tableB and the situation is the primary key of tableA is a foreignkey of tableB and the primary key of tableB is a foreignkey of tableA.in this type of situation how we can insert data into 2 tables called tableA,tableB.pls tell me how can i handle this situation very efficiently.explain in detail........plzzzzz.
    Saturday, January 27, 2007 2:54 AM

Answers

  • User1335583151 posted

    Hello my friend,

    What you are suggesting is a many-to-may relationship, which is bad.  If you really need to do it this way, you need to remove the constraint so that the fields are there to query but the relationships are not enforced. 

    The other way, which is the best practice way, is to break the one-to-many relationship by creating a middle table.  Suppose a customer could buy many products and a product could be purchased by many customers.  The middle table would be something like an OrderLine table.  Now, a customer can have 1 or many order lines but an order line belongs to only 1 customer.  And a product can be part of 1 or many order lines but an order line will only contain 1 product.  If the customer buys 3 products as part of an order, there would be 3 order lines. 

    Hope this helps

    Kind regards

    Scotty

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 27, 2007 6:06 AM

All replies

  • User1335583151 posted

    Hello my friend,

    What you are suggesting is a many-to-may relationship, which is bad.  If you really need to do it this way, you need to remove the constraint so that the fields are there to query but the relationships are not enforced. 

    The other way, which is the best practice way, is to break the one-to-many relationship by creating a middle table.  Suppose a customer could buy many products and a product could be purchased by many customers.  The middle table would be something like an OrderLine table.  Now, a customer can have 1 or many order lines but an order line belongs to only 1 customer.  And a product can be part of 1 or many order lines but an order line will only contain 1 product.  If the customer buys 3 products as part of an order, there would be 3 order lines. 

    Hope this helps

    Kind regards

    Scotty

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 27, 2007 6:06 AM
  • User1178851682 posted

    i think the tables are not design correctly

    To me it looks like composite table...

    i think have correct database design will make code easier

    Saturday, January 27, 2007 6:18 AM
  • User1816320197 posted

    Hi the same happend to me.In intervirew they asked to write TSQL code for inserting the data without disabling the constraint.

     

    he told that with 3 dml statements its possible. can any one help. 

    Monday, December 27, 2010 2:32 AM