• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 810
  • Last Modified:

DataSet with three relational DataTables - enforce unique row entries?

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
craigewens
Asked:
craigewens
  • 5
  • 2
1 Solution
 
Munawar HussainPrincipal Software EngineerCommented:
hi

cant you add fake keys as unique?

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

-thanks

0
 
craigewensAuthor Commented:
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
 
Munawar HussainPrincipal Software EngineerCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
craigewensAuthor Commented:
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
 
craigewensAuthor Commented:
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
 
dportasCommented:
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
 
craigewensAuthor Commented:
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
 
craigewensAuthor Commented:
Apologies; I mistyped your name and cannot edit posted on EE.
Sorry dportas.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now