foreign key constraint in sql

Question

Hi everyone I have 2 tables in sql server with primary keys set to identity. They are related and work fine. I then created a form in vb 2008 and tried inserting some values into my database the respective primary keys work but the primary key in the parent table wont show up in the child table.I did create a relationship in vb using ado.net and all the details of my table are defineed in the data table. For example cust tables (custid,name,..) book table(bookid,bookname,..,custid)

in vb my insert statement is something like Insert into cust(name) values(@name) insert into book(bookname) values(@bookname). I do not include the id columns as they auto generate in the database(tables). My question is that how do i get to insert the custid in the book table when the data is stored back into the tavles in my database.

Please advice with an example as im not half as good as you guys.

Kind Regards

Answer 1

You have to know which customer you want to associate with the book before INSERTing the book. If you don't know before hand, you can't. So somewhere in your Form there should be a way to select a customer. Then when you create a book, you grab that customer's ID and insert it along with the other book info.

Answer 2

You don't actually say that you created a foreign key constraint between the two tables!

You need to:

• Ensure that you create an explicit foreign key on the BOOK table to point to a customer in the CUST table.

• First insert the customer.

• Then find out what the customer's auto-generated ID was. That value is in @@IDENTITY. Store it somewhere e.g. @CUSTID.

• Insert the book, specifying @CUSTID as the customer's ID.