Solved

DataSet with three relational DataTables - enforce unique row entries?

Posted on 2009-05-15
9
804 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
ID: 24400045
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
ID: 24401690
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
ID: 24406567
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:craigewens
ID: 24410310
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
 
LVL 2

Accepted Solution

by:
craigewens earned 0 total points
ID: 24413160
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
ID: 24505934
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
ID: 24515685
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
ID: 24515691
Apologies; I mistyped your name and cannot edit posted on EE.
Sorry dportas.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 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