Avatar of ncw
ncw asked on

Inserting records into an MSSQL table with combined primary key

If I have 3 tables eg products, products_to_categories, and categories. If I insert a record into table products then I also want to insert a corresponding record into products_to_categories, and categories. Each of these talbes has a primary key, products.product_id, categories.category_id, then for products_to_categories there's a combined key (or clustered it might be called) that is product_id and category_id, this table being used to link products to categories. (I'm converting an existing MySql Db)

Now If I insert a record into categories then I think I can probably set the categorie_id value if the column has an IDENTITY (is that right), but how do I set the values of the 2 id fields in products_to_categories?  (I'm an MSSQL beginner)
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
ncw

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

the 2 fields in the link table are simple field, having the same data type as the field being the primary key in the related table, except the identity() property if that were set.

now, let's clarify: once you insert a row in the categories (or products), you want to automatically insert rows into the  products_to_categories table?
does not seem correct, as for a new category, you cannot insert all the products at once, or just 1 product?
same for a product, although there you might want to assign the product to some kind of "default" category, like a "draft" category...

please clarify.
ASKER
ncw

Sorry, I was not correct in my description, you're right I don't need to insert a record in the category table, my problem only lies with inserting the record into the products_to_categories table. Are you saying that really the 2 id fields in the products_to_categories link table don't need to be a composite key?

My code inserts a record into the products table, then gets the id of the last record, and I want to then insert a record into the link table with the last product_id and correct category_id.

Dany Balian

hi ncw,
1. how would you know the correct category id?
2. the composite key is to only make sure that every product gets added to the same category only once... but it's not necessary if you add restrictions on the software level.
3. the way to do it is very simple:
a- first approach: using software level coding
   - insert the product into the products record
   - get the id of the product inserted (software level)
   - get the corresponding category id
   - insert product,category into products_to_Categories table
b- second approach: using database triggers
   - you can add a trigger that fires on insert of any records into the products category... that trigger will automatically insert the corresponding category into the products_to_categories table.

what coding languages do you use? and do you want to use triggers?

waiting for your feedback...

hope this was clear enough,

cheers,

dan
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dany Balian

correction - second approach: you can add the trigger that fires on insert of any records into the products table (not category)
ASKER CERTIFIED SOLUTION
cpleong99

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ncw

I don't want to get into setting up triggers, and assume the obtaining of the category_id is done elsewhere in the code. I'm adapting some existing Php scripts to work with MSSQL.

cpleong99: I'm interested in your approach, I didn't realise there was an easy way to get the last insert id, I will try it out. What about the fields in products_to_categories, I assume they would be standard int fields ie not PK's and without identity?
cpleong99

You can use CategoryID + ProductID as PK. Since these 2 values doesn't change.

Not sure what other method you use to get the latest ID,however,  if you are using select max(id) from table, then in a multiuser, multi inserting environment, you might get wrong ID.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ncw

But when product_id and category_id are a combined PK and I try and do an insert and set those field values I get an error. Or does the @ solve that?
Guy Hengel [angelIII / a3]

>But when product_id and category_id are a combined PK and I try and do an insert and set those field values I get an error.
please show the code and the error.
ASKER
ncw

I've used your Select @ProductID = @@Identity and when I insert a record in the product_description table setting the id value, as long as I use SET IDENTITY_INSERT pages_description ON before and SET IDENTITY_INSERT pages_description OFF after then it all seems to work - thanks!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck