none
Insert into Many to Many tables RRS feed

  • Question

  • I hope this is the right forum for the question.

    I have a small SQL database mainly with three Tables, A) Client table, B) Cars table and C) Join table.
    Why a Join table? Because Cars can be owned by many Clients and Clients can own many Cars.

    Right now, my database use no Foreign Key constraints, I do all the hard work by hand. I am new to SQL Server. I read that a Foreign Key constraint with CASCADE would hell simplify the Delete logic, say when a Car is dropped from the database, I would not hunt Clients in the Join table for deletion, it would happen automatically. 

    But what about INSERTING? If a new Car comes in the door, I can have anywhere from one to 10 Clients interested. I don't suppose Referential Integrity can make my life simpler, in terms of programming: I still have to create INSERT into the Car Table for the new Car *and* painstakingly iterate the Join table with as many INSERT as necessary to accommodate the number of Clients?

    For example, new Car A with 10 existing Clients :

    A) INSERT CarName, CarYear, CarPrice in Car table
    B) Retrieve SCOPE IDENTITY (from Car A newly added record )
    C) INSERT CarID=123, ClientID=10 into Join table
    D) INSERT CarID=123, ClientID=15 into Join table
    E) INSERT CarID=123, ClientID=34 into Join table
    G) INSERT CarID=123, ClientID=87 into Join table

    … and so on for every ClientID in the list of 10 Clients.

    I have a small VisualBasic application that does these INSERTs into a Dataset that uses TableAdapters to automatically send the data to SQL Server and this morning, while testing, I tried to save a new Car into the Dataset, with a bunch of Clients and DID NOT GET AN ERROR: the new Car was indeed inserted into the Car table BUT the 10 or so Clients did not get added in the database. So, of course, when I tried to navigate to that new Car record, I was shocked to find that the Client information had been lost. 

    Any help is appreciated. From my humble understanding of SQL, I don't see how setting up a Foreign Key relation between the Car table and the Join table would help prevent that insertion error? The responsibility for creating the INSERT statements into the two separate tables is still mine, right?

    Wednesday, May 20, 2020 6:23 PM

Answers

  • There is no magic to this.

    You have a parent table and a child table.  You need to insert the parent table first, then insert all the children referencing the parent. 

    A FK will not help. It will just enforce the fact the parent always exists.

    • Marked as answer by roger.breton Thursday, May 21, 2020 3:54 AM
    Wednesday, May 20, 2020 7:39 PM
    Answerer

All replies

  • There is no magic to this.

    You have a parent table and a child table.  You need to insert the parent table first, then insert all the children referencing the parent. 

    A FK will not help. It will just enforce the fact the parent always exists.

    • Marked as answer by roger.breton Thursday, May 21, 2020 3:54 AM
    Wednesday, May 20, 2020 7:39 PM
    Answerer
  • What do you mean by Cars can be owned by many Clients? Do you mean simultaneously or historically? If you mean simultaneously, I know that a car can be registered by more than one person but is there a limit to that? Perhaps you need to only support a maximum of three registrants/owners. And you can have three foreign keys in the car record for them. If you mean historically then you should have a table showing a car's ownership history.

    As for clients that are interested in a purchase, that sounds like a contact management system, such as in Best Contact Management Software. If you want to develop your own that is integrated with your other data then perhaps the other software will inspire you.

    Something else to use as inspiration is Industry Data Models. There are many sample database designs there. The following can probably help but there are more than 20 sample sales database designs there.

    First design the database. I know you are asking for help with the implementation, but the first step is to get a good design and you probably can improve that. I assume you will not implement everything initially but the better your design now, the easier it will be to improve.Something else you can do to make things easier in the future is to learn Entity Framework. It will take time to learn but save time in the future.



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, May 20, 2020 8:33 PM
  • Sam,

    I guess it does not make sense that Cars can be owned by many Clients "simultaneously" -- my bad. You are right, Cars can be owned by many Client "historically", a given Cadillac 1971 was owned by Client X in 1971 and then by Client Y in 1974 and by Client Z in 1981. It does not make sense otherwise. 

    Thank you so much for your advice :-)

    Wednesday, May 20, 2020 8:38 PM
  • Tom, that is what I suspected but, not having whole lot of experience in database programming, I wasn't sure. That aspect of database operation is not discussed much on the internet :(
    Wednesday, May 20, 2020 8:40 PM