put value into foreign key table

Question

Post reply

put value into foreign key table

Sarfaraj Ahmed

SSC-Addicted

Points: 447
More actions
October 24, 2007 at 7:01 am

#61393

Im going to ask you a very stupid and simple question, please give me your valuable instructions...
dbo.Books is PK table
dbo.Category is PK table
dbo.BooksCategory is FK table
I got bookID=1001 and CategoryID=101.
my question is when should I insert data into dbo.BooksCategory table
1. insert data into dbo.books then dbo.bookscategory
2. insert data into dbo.bookscategory then dbo.books
is their anything i can do on my sql server 2005 database or i have to write a function to insert data into dbo.bookscategory from asp.net
waiting for a good instructions.
Sarfarj Ahmed

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dfalir SSCrazy Points: 2486 More actions · Answer 1

Hi,

can you be a bit more specific? what do you mean when to insert data?

if you receive (or have) a boo that belongs to a category, then you insert the book in the book table and then you insert the book and category in the boos_category (mediator) table.

does this answer your question?

"If you want to get to the top, prepare to kiss alot of bottom"

Lowell SSC Guru Points: 323518 More actions · Answer 2

pointing out the obvious here, but this might help:

the book must exist first in your dbo.books table.

after it has been added, you can realte the book to one or more categories that exist in dbo.categories.

so your first statmenet, dbo.books, THEN bookcategories is correct.

if a category doesn't exist yet (ie you add a book related to "Sports")

you'd need to add the category, and then finally realate the book to categories in the FK table (dbo.bookCategories?)

to add something automatically, you'd need to have an assumption of some type..ie all books are added to category "General"

you'd know the business rule better than us...tell us the business rule,a nd we can help a bit witht he implementation.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 3

Dfalir (10/24/2007)
Hi,
can you be a bit more specific? what do you mean when to insert data?
if you receive (or have) a boo that belongs to a category, then you insert the book in the book table and then you insert the book and category in the boos_category (mediator) table.
does this answer your question?

need to make a relationship between Books and Category table

Canegory tabel has all Category like

CategoryID =100, CategoryName= programming and soo..........

Now I am going to add a New book into books table. and this book category is programming and bookId= 1001

now i need to put some information into BooksCategory table where I am able to know bookid=1001 is programming category book

how should i put data into BooksCategory table ,

BooksCategory table has column name

AutoID, BookID and CategoryID

If you understand my question then please tell me

Thanks in Advance

Dfalir SSCrazy Points: 2486 More actions · Answer 4

ok, before i begin i suppose that a book can belong to MORE than one category. Otherwise you do not need the books_categorytable. Just add a colum in the books table that says categoryid.

Now if a book belongs to more than one category the procedure you have to follow is this.

1) Insert into the books table the values that relate to the book. That is bookid and book name.

Insert into Books

(bookid, book description)

values

(1001, Advanced Programming(supposed title) )

2) insert into the bookscategory table the Bookid and CategoryID

Insert into Books_Category

(BookId, CategoryID)

Values

(1001, 100)

if you want to add the book also to the cooking section,

then

Insert into Books_Category

(BookId, CategoryID)

Values

(1001, 300) where 300 is the cooking category

does this cover you?

"If you want to get to the top, prepare to kiss alot of bottom"

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 5

Books Table:

AutoID, ISBN(PK), BookTitle

Category Table: (added all category informations)

AutoID, CategoryID(PK), CategoryName

BooksCategory Table:

AutoID, ISBN(FK), CategoryID(FK)

Now I am going to add new book into books table

Addbooks.aspx has drop down list name ddlCategory where you can select category name. when you select category name then you get the categoryID. Also you will get ISBN from any book

I don’t want to add a column name categoryID into Books table this is why I have added a new table name BooksCategory

From addBooks.aspx page you a button name AddNewBooks when you click addnewBooks button then at first it will add books information into books table then ISBN and CategoryID will be added into booksCategory table.

My question is their anything I can design my booksCategory table where data will automatically added into booksCategory table which will take ISBN from Books table and CategoryID from Category table

Thanks in Advance