Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DataSet with three relational DataTables - enforce unique row entries?

Posted on 2009-05-15
9
Medium Priority
?
808 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
[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
  • 5
  • 2
9 Comments
 
LVL 12

Expert Comment

by:Munawar Hussain
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:Munawar Hussain
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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