Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Junction Table

Posted on 2009-05-19
12
Medium Priority
?
1,138 Views
Last Modified: 2013-11-05
Good-day,

I am attempting to create a very simple database that uses a Junction Table. (see diagram)

Basically, what I would like to achieve is to assign one product to multiple categories;  When I run the code bellow, (in C#) I receive a constraint error;

I am wondering what  specifically is wrong with the diagram, and how I can change the DB to allow a product in multiple categories?

Thanks for your time
var adapterManager = new TableAdapterManager
            {
                ProductCategoryTableAdapter = new ProductCategoryTableAdapter(),
                CategoryTableAdapter = new CategoryTableAdapter(),
                ProductTableAdapter = new ProductTableAdapter()
            };
 
            adapterManager.UpdateAll(_dataSet);

Open in new window

help.gif
0
Comment
Question by:PastorDwayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 

Expert Comment

by:madhuottapalam
ID: 24427910
If you are getting constraint error , you must be inserting child first and then Master table. Do master first and then child

Madhu
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24427947
The INSERT order should be like Product, Category and ProductCategory or Category, Product and ProductCategory.

If you try to insert in ProductCategory before Product or Category you may receive a Constraint violation as its Product and Category ID's are not available in those tables.

Hope this helps
0
 

Author Comment

by:PastorDwayne
ID: 24427972
Thanks for your comments;

How would I change the following code to produce this result?

Thanks again;
var adapterManager = new TableAdapterManager
            {
                ProductCategoryTableAdapter = new ProductCategoryTableAdapter(),
                CategoryTableAdapter = new CategoryTableAdapter(),
                ProductTableAdapter = new ProductTableAdapter()
            };
 
            adapterManager.UpdateAll(_dataSet);

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:PastorDwayne
ID: 24427984
Also, the exact error I am getting is:

ConstraintException was unhandled
Column 'ProductID, CategoryID' is constrained to be unique.  Value '0, 2' is already present.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24427987
What are you trying to do is a Hierarchical update.
Try this one out.
var adapterManager = new TableAdapterManager
            {
                CategoryTableAdapter = new CategoryTableAdapter(),
                ProductTableAdapter = new ProductTableAdapter(),
                ProductCategoryTableAdapter = new ProductCategoryTableAdapter()
            };
 
            adapterManager.UpdateAll(_dataSet);

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24427994
<< ConstraintException was unhandled
Column 'ProductID, CategoryID' is constrained to be unique.  Value '0, 2' is already present. >>

Then it means that you have values 0, 2 already in ProdcutCategory table and you are trying to update the same value 0, 2 for some other record.

Since you have ProductID and CategoryID combination as Primary Key or Unique Key combination you might obtain that error.
0
 

Author Comment

by:PastorDwayne
ID: 24428044
Thanks rrjegan17, I removed the two primary keys from the junction table, and ordered the update as you have mentioned;  however now I receive this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProductCategory_Product". The conflict occurred in database "MyDB", table "dbo.Product", column 'ProductID'.
The statement has been terminated.

Why would this be?  Also, is it alright for a SQL table to have no primary keys?

Thanks for your time
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24428073
<< The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProductCategory_Product". The conflict occurred in database "MyDB", table "dbo.Product", column 'ProductID'.
The statement has been terminated. >>

This means that you are trying to insert some records into ProductID column of ProductCategory table which is not present in the Product table. You have to first Insert some Products into Product table, some Categories into Category table and after that insert records into ProductCategory table to resolve this out.

<< I removed the two primary keys from the junction table >>

Does that mean deleted the records or removed the Primary keys itself. Have the Primary key constraint to ensure data integrity.
0
 

Author Comment

by:PastorDwayne
ID: 24428124
Thanks rrjegan17;

I re-added the Primary keys to the junction table, and there are no records in any of the tables.
 
No I receive this error

ConstraintException was unhandled
Column 'ProductID, CategoryID' is constrained to be unique.  Value '0, 2' is already present.

upon executing the following code:
_dataSet.ProductCategory.AddProductCategoryRow(_itemsRow, _categoriesRow);

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 820 total points
ID: 24428176
Ok.. I hope your C# code is trying to insert same record twice or some thing like that because I tried the following and it works fine for me

create table category ( categoryid int identity primary key, subcategoryid int,
name varchar(50));
create table product ( productid int indentity primary key, name varchar(250), desc varchar(max));
create table productcategory ( productid int, categoryid int);

alter table productcategory add constrain pk_prodcat primary key ( productid, categoryid);

insert into category (subcategoryid , name )values ( 1, 'test');
insert into category (subcategoryid , name )values ( 2, 'abc');

insert into product ( name, desc) values ( 'abc','test');
insert into product ( name, desc) values ( 'cde','xyz');

insert into productcategory values ( 1,1);
insert into productcategory values ( 2,2);
insert into productcategory values ( 2,1);

update productcategory
set productid = 1,
      categoryid = 2
where productid = 2
and categoryid = 1

I have all valid scenarios as mentioned by you and is working fine.
Revert If I am wrong anywhere in my assumption
0
 

Author Comment

by:PastorDwayne
ID: 24428220
Yes, that's exactly it;  It was trying to add duplicate records,

Thanks rrjegan17 very much for your help!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24428645
Welcome
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question