How to create a constraint with multiple columns using OLEdb

Pat Hanks 141

The use case is for an Access database using OLEdb via C#. I need to build the tables or alter afterward to establish the relationships between a couple tables. I am able to get the relationship with one column in the table to another column in a foreign table. I am using this syntax for the constraint, CONSTRAINT FK_indexname REFERENCES tablename (columnName) ON DELETE CASCADE. However I need to have a single column (ID) to multiple columns in a foreign table. The use is I have a item table for all the items used and a recipe table where several columns will reference the item table. Here is what I built in Access that I need to understand the proper syntax to use in my code.

Thanks for your help

Jack J Jun 24,286 Reputation points Microsoft Vendor

2023-01-03T06:13:06.327+00:00

@Pat Hanks , Welcome to Microsoft Q&A, I am not very clear about your meaning. Did you want to c# code to create a constraint with multiple columns? Why not use the access to create it directly?
Pat Hanks 141 Reputation points

2023-01-03T13:44:37.763+00:00

Apologies I didn't explain the entire situation. This is part of the initialization process for the application. It will create the database & tables upon installation/initial run, as well any updates will modify the database accordingly. I was able to set the relationship via Access as pictured. I just need to understand the syntax for the constraint to achieve the same result via code. I have reviewed the Microsoft online documentation, but can't seem to get it to work. I am trying to establish a one to many for the ID of one table to several columns in another.

Thanks
AgaveJoe 26,191 Reputation points

2023-01-03T14:18:48.95+00:00

I have reviewed the Microsoft online documentation, but can't seem to get it to work.

Can you provide code that illustrates what is not working and the Microsoft documentation you are reviewing?

I am trying to establish a one to many for the ID of one table to several columns in another.

Typically this is handled by writing a "join" query that selects columns from each table creating a record set.
Pat Hanks 141 Reputation points

2023-01-03T14:24:41.867+00:00

The goal is to setup the relationship to prevent deletion of the item and orphaning the other records. If there is a better way to achieve this I am open to any solutions.
AgaveJoe 26,191 Reputation points

2023-01-03T14:30:15.197+00:00

The goal is to setup the relationship to prevent deletion of the item and orphaning the other records. If there is a better way to achieve this I am open to any solutions.

Constraints are database objects and best handled by the database. Can you explain why you want to handle this in C#?

Are you asking how to create a constraint in using C# and OleDB?

CONSTRAINT clause (Microsoft Access SQL)
Pat Hanks 141 Reputation points

2023-01-03T14:56:11.197+00:00

I am trying to establish the constraint in C# for the table create / modification. I certainly do not want to manage the constraint. Let the DB do it's thing :) I am able to get the syntax working for a single column. I need help with the syntax for multiple columns in the same constraint statement. I just can't seem to get it working.
AgaveJoe 26,191 Reputation points

2023-01-03T15:20:11.02+00:00

After taking a closer look at the screenshot, I believe the problem is the design in not normalized which is causing unnecessary complexity.

Database normalization

Rather than having 5 columns in the reload recipe table there should be a new table that contains the RecipeID and ItemID. This new table will have 5 records that joins the two tables. This is called a many-to-many relationship.
Pat Hanks 141 Reputation points

2023-01-03T21:40:17.477+00:00

Ok I see where you are going with this. I assume for each recipe record I will need to add the 5 records to the intermediate table. Also I assume I will establish a constraint from the Recipe & Item tables to the intermediate table. Am I on the right path with this?