Solved

DataSet with three relational DataTables - enforce unique row entries?

Posted on 2009-05-15
9
800 Views
Last Modified: 2013-12-17
Hi,
I have DataSet defined within Visual Studio 2005, I mainly use the dataset to databind to a datagridview implemented in C#.

Table 1 has a number of columns (for example purposes I'm only showing as 3)
col1 is a primary key unique autonumber
col2 is an integer linked to the primary key of table 2
col3 is an integer linked to the primary key of table 3

Table 2 has 2 columns
col1 is a primary key unique autonumber
col2 is a unique string

Table 3 has 2 columns
col1 is a primary key unique autonumber
col2 is a unique string

Since Table1 is populated into a datagridview I have tables 2 and 3 accessed via comboboxcolumns within the dgv and displaying the string rather than the numbers.

With me so far? ... ok so all that is working fine but I've been informed that the combination of table2 and table3 indexes within table1 must be unique.
I initially thought that I just needed to make the col2 and col3 as unique within the DataSet designer but that just meant I couldn't use col2 or col3 twice rather than a unique combination of the two.

Eg within Table1 col2 and col3
1,1
1,2
1,3
2,1
2,2
2,3
is fine, but
1,1
1,2  <----
1,2  <----
2,2
is bad

Can someone please explain a way to achieve my goal? Ideally i'd like to achieve it within the DataSet layer enforcing the design rather than within a GUI at entry and looping through and checking.

If you have any queries please ask.. it's very difficult to describe how a Dataset object looks in text.
0
Comment
Question by:craigewens
  • 5
  • 2
9 Comments
 
LVL 12

Expert Comment

by:needo_jee
Comment Utility
hi

cant you add fake keys as unique?

just add a column something id and fill unique values and let it go...

-thanks

0
 
LVL 2

Author Comment

by:craigewens
Comment Utility
Hi Needo Jee... thank you for reply, but i don't think i understand your suggestion.

Sounds like you are suggesting i create a new column... but within which table?
What do i fill this new column with?
How does it make sure the combination of col2 and col3 within table 1 for each row is unique?

Thank you for your time
0
 
LVL 12

Expert Comment

by:needo_jee
Comment Utility
hi,

I am sorry for inconvenience, my understanding was different as I took just bird eye view of one  segment.

After looking over, I found that the current flow as described seems correct and it should work.

you have three tables. combining results of any numbers of table still has a unique combination always therefore it should work correctly

IN TABLE 1
you should not have any issue as PK of table is unique



as per your YOUR QUESTION  (goal)
 I've been informed that the combination of table2 and table3 indexes within table1 must be unique.

just look at combination of three columns
PK_tbl1, pk_tbl2, PK_tbl3

they will be unique always.. .

but if your table three has more than one record against 1 record of table 2 then logically it should duplicate keys in table1 as you have described above and that is correct.

to your requirement that can be some alternate(if problem is described) but the duplication is not a problem its normal flow as per normalization rules. just looking at column 2 and three does not seems right .. because adding pk of table 1 makes a three column unique combination as well....

0
 
LVL 2

Author Comment

by:craigewens
Comment Utility
Hi Needo Jee,

I still don't exactly see what you are advising me to do :(

Table1 col1 has to be a PK autonumber since it is used by other tables... we cannot adjust Table1 col1.

Making Table1 col2 and col3 FK's of Table2col1 and Table3col1 doesn't work on the basis that the unique aspect is relevant for the column not the row. Adding more than one PK or FK to Table1 doesn't mean that the combination of those three columns on a row-by-row basis have to be unique... it simply means the data in col1 col2 and col3 cannot be referenced more than once.

Here is a GOOD Table1 example:
col1,col2,col3
1,1,1
2,1,2
3,1,3
4,2,1
5,2,2
6,2,3

Here is a BAD Table1 example:
col1,col2,col3
1,1,1
2,1,2  <---- col2 and col3 = 1,2 = BAD
3,1,2  <---- col2 and col3 = 1,2 = BAD
4,2,1
5,2,2
6,2,3

Although in the BAD example rows 2 and 3 are unique; it's col2 and col3 which are the same and therefore undesired. By making col2 a FK all that it would mean is that i could only use a number once in that column.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Accepted Solution

by:
craigewens earned 0 total points
Comment Utility
I have implemented, at the data insertion layer (GUI), code which enforces that I don't get duplicate combinations of col2 and col3 as this allows me to progress with the rest of my task; but I would love a database method if anyone knows of one.
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
I think the following is what Jee has suggested in standard SQL, ie a compound key on the two columns. Your dialect of SQL may vary. Please tell us what DBMS you are using if you need more help.

ALTER TABLE table1 ADD CONSTRAINT foo1 UNIQUE (col2, col3);
0
 
LVL 2

Author Comment

by:craigewens
Comment Utility
Hi dortas,

Thank you for your reply.
I am not really using a DBMS as such...
I'm working within Visual Studio 2005. You can add a new item from the VS templates which is a DataSet.
This creates an XSD (aka Schema) file and provides an interface to add DataTable's, Query's and Relations.
It's these DataTables and Relations that I'm using.
When linking the tables; I am creating 'Both Relation and Foreign Key Constraint' relations but there is no option for using SQL statements or adding qualifies like UNIQUE that I am aware of.
0
 
LVL 2

Author Comment

by:craigewens
Comment Utility
Apologies; I mistyped your name and cannot edit posted on EE.
Sorry dportas.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now