473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DataSet DataTable Unique constraint

Hi there,

Is it possible to add a unique constraint on two columns in a table, so that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?

i.e.
column1 column2
1 1
1 2
2 1
1 1 <-- should not be able to add this record as combination
already exists,

thanks for any help
regds
Brian
Nov 17 '05 #1
7 6617
Brian,

Yes, it is. When creating the UniqueConstraint instance to apply to a
DataTable, you can pass in an array of DataColumn instances representing the
columns, that together, should be unique.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
Hi there,

Is it possible to add a unique constraint on two columns in a table, so
that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?

i.e.
column1 column2
1 1
1 2
2 1
1 1 <-- should not be able to add this record as combination
already exists,

thanks for any help
regds
Brian

Nov 17 '05 #2
Brian,
Have you tried something like:

try
{
DataTable table = new DataTable();
table.Columns.Add("column1", typeof(int));
table.Columns.Add("column2", typeof(int));
Constraint constraint = new UniqueConstraint("constraint1",
new DataColumn[] {table.Columns["column1"],
table.Columns["column2"]}, false);
table.Constraints.Add(constraint);

table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {1,1});
}
catch (Exception ex)
{
Debug.WriteLine(ex, "Exception");
}

Hope this helps
Jay

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
| Hi there,
|
| Is it possible to add a unique constraint on two columns in a table, so
that
| the constraint is a composite of the two? i.e. these two columns together
| should be unique...?
|
| i.e.
| column1 column2
| 1 1
| 1 2
| 2 1
| 1 1 <-- should not be able to add this record as combination
| already exists,
|
| thanks for any help
| regds
| Brian
Nov 17 '05 #3
Hi Jay,

thanks for your help, i had tried this but that actually ensures that that
each of the columns is unique, not that the combination of both is unique.

found a solutions anyways, i added a new key on the dataset that had the two
columns i was interested in.

thanks for your help
brian

"Jay B. Harlow [MVP - Outlook]" wrote:
Brian,
Have you tried something like:

try
{
DataTable table = new DataTable();
table.Columns.Add("column1", typeof(int));
table.Columns.Add("column2", typeof(int));
Constraint constraint = new UniqueConstraint("constraint1",
new DataColumn[] {table.Columns["column1"],
table.Columns["column2"]}, false);
table.Constraints.Add(constraint);

table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {1,1});
}
catch (Exception ex)
{
Debug.WriteLine(ex, "Exception");
}

Hope this helps
Jay

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
| Hi there,
|
| Is it possible to add a unique constraint on two columns in a table, so
that
| the constraint is a composite of the two? i.e. these two columns together
| should be unique...?
|
| i.e.
| column1 column2
| 1 1
| 1 2
| 2 1
| 1 1 <-- should not be able to add this record as combination
| already exists,
|
| thanks for any help
| regds
| Brian

Nov 17 '05 #4
hi nicholas

thanks for your help, i had tried this but that actually ensures that that
each of the columns is unique, not that the combination of both is unique.

found a solutions anyways, i added a new key on the dataset that had the two
columns i was interested in and this works just fine

thanks for your help
brian

"Nicholas Paldino [.NET/C# MVP]" wrote:
Brian,

Yes, it is. When creating the UniqueConstraint instance to apply to a
DataTable, you can pass in an array of DataColumn instances representing the
columns, that together, should be unique.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
Hi there,

Is it possible to add a unique constraint on two columns in a table, so
that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?

i.e.
column1 column2
1 1
1 2
2 1
1 1 <-- should not be able to add this record as combination
already exists,

thanks for any help
regds
Brian


Nov 17 '05 #5
Brian,
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
Have you? What flavor of the framework?

In VS.NET 2003 (.NET 1.1 SP1) it ensures that the combination of both
columns are unique! In your example it throws an exception when you try to
add the second "1, 1" pair. If it was ensuring each column was unique then
it would fail when you attempted to add "1,2", as "1,1" already exists.

| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
Note the third parameter to the UniqueConstraint construtor I used indicates
if this constraint is the primary key or not...
Try the following adds, which line throws the constraint exception?

table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {1,3});
table.Rows.Add(new object[] {1,4});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {2,2});
table.Rows.Add(new object[] {2,3});
table.Rows.Add(new object[] {2,4});

table.Rows.Add(new object[] {1,1});

It again should be:
table.Rows.Add(new object[] {1,1});

As that line has the duplicate.

Hope this helps
Jay

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:9D**********************************@microsof t.com...
| Hi Jay,
|
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
|
| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
|
| thanks for your help
| brian
|
| "Jay B. Harlow [MVP - Outlook]" wrote:
|
| > Brian,
| > Have you tried something like:
| >
| > try
| > {
| > DataTable table = new DataTable();
| > table.Columns.Add("column1", typeof(int));
| > table.Columns.Add("column2", typeof(int));
| > Constraint constraint = new UniqueConstraint("constraint1",
| > new DataColumn[] {table.Columns["column1"],
| > table.Columns["column2"]}, false);
| > table.Constraints.Add(constraint);
| >
| > table.Rows.Add(new object[] {1,1});
| > table.Rows.Add(new object[] {1,2});
| > table.Rows.Add(new object[] {2,1});
| > table.Rows.Add(new object[] {1,1});
| > }
| > catch (Exception ex)
| > {
| > Debug.WriteLine(ex, "Exception");
| > }
| >
| > Hope this helps
| > Jay
| >
| > "Brian Keating" <Br**********@discussions.microsoft.com> wrote in
message
| > news:E6**********************************@microsof t.com...
| > | Hi there,
| > |
| > | Is it possible to add a unique constraint on two columns in a table,
so
| > that
| > | the constraint is a composite of the two? i.e. these two columns
together
| > | should be unique...?
| > |
| > | i.e.
| > | column1 column2
| > | 1 1
| > | 1 2
| > | 2 1
| > | 1 1 <-- should not be able to add this record as
combination
| > | already exists,
| > |
| > | thanks for any help
| > | regds
| > | Brian
| >
| >
| >
Nov 17 '05 #6
Hi Jay,

When i think back yoiu are more than likely right, acually yes i would
agree, you are right.

My mistake was that i didn't really have 2 columns on the unique key, i had
3! and it was only when i created the extra key that i added this column, so
basically my unique constraint with with 2 coulmn had duplicated when i went
to load it and so i said... shit ... not working and went back to the drawing
board.

thanks again,
Brian
"Jay B. Harlow [MVP - Outlook]" wrote:
Brian,
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
Have you? What flavor of the framework?

In VS.NET 2003 (.NET 1.1 SP1) it ensures that the combination of both
columns are unique! In your example it throws an exception when you try to
add the second "1, 1" pair. If it was ensuring each column was unique then
it would fail when you attempted to add "1,2", as "1,1" already exists.

| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
Note the third parameter to the UniqueConstraint construtor I used indicates
if this constraint is the primary key or not...
Try the following adds, which line throws the constraint exception?

table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {1,3});
table.Rows.Add(new object[] {1,4});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {2,2});
table.Rows.Add(new object[] {2,3});
table.Rows.Add(new object[] {2,4});

table.Rows.Add(new object[] {1,1});

It again should be:
table.Rows.Add(new object[] {1,1});

As that line has the duplicate.

Hope this helps
Jay

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:9D**********************************@microsof t.com...
| Hi Jay,
|
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
|
| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
|
| thanks for your help
| brian
|
| "Jay B. Harlow [MVP - Outlook]" wrote:
|
| > Brian,
| > Have you tried something like:
| >
| > try
| > {
| > DataTable table = new DataTable();
| > table.Columns.Add("column1", typeof(int));
| > table.Columns.Add("column2", typeof(int));
| > Constraint constraint = new UniqueConstraint("constraint1",
| > new DataColumn[] {table.Columns["column1"],
| > table.Columns["column2"]}, false);
| > table.Constraints.Add(constraint);
| >
| > table.Rows.Add(new object[] {1,1});
| > table.Rows.Add(new object[] {1,2});
| > table.Rows.Add(new object[] {2,1});
| > table.Rows.Add(new object[] {1,1});
| > }
| > catch (Exception ex)
| > {
| > Debug.WriteLine(ex, "Exception");
| > }
| >
| > Hope this helps
| > Jay
| >
| > "Brian Keating" <Br**********@discussions.microsoft.com> wrote in
message
| > news:E6**********************************@microsof t.com...
| > | Hi there,
| > |
| > | Is it possible to add a unique constraint on two columns in a table,
so
| > that
| > | the constraint is a composite of the two? i.e. these two columns
together
| > | should be unique...?
| > |
| > | i.e.
| > | column1 column2
| > | 1 1
| > | 1 2
| > | 2 1
| > | 1 1 <-- should not be able to add this record as
combination
| > | already exists,
| > |
| > | thanks for any help
| > | regds
| > | Brian
| >
| >
| >

Nov 17 '05 #7
Hi Nicholas,

you were correct, i actually had 3 coulmns so when i went to load it i got
duplicates... dough!! i didn't design the database so i only noticed there
were 3 columns in the key at a later stage. (creating the extra key actually
but it's only hit me now after seeing the Jay B. Harlow told me that
UniqueContatrian was really what i though it was.

thanks brian

"Nicholas Paldino [.NET/C# MVP]" wrote:
Brian,

Yes, it is. When creating the UniqueConstraint instance to apply to a
DataTable, you can pass in an array of DataColumn instances representing the
columns, that together, should be unique.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
Hi there,

Is it possible to add a unique constraint on two columns in a table, so
that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?

i.e.
column1 column2
1 1
1 2
2 1
1 1 <-- should not be able to add this record as combination
already exists,

thanks for any help
regds
Brian


Nov 17 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Marty | last post by:
Hi, Ok I use the OLEDBConnector and dataset to retrieve data from my Access DB. I have a problem to read/parse the dataset and I would like to know if I am using the right object to reach my...
2
by: TechNoHick | last post by:
I am using a dataset that I fill with XML data. When I try and use the Dim curRow As DataRow = dtGalleries.Rows.Find(GalleryId) I keep getting an error that the primary key doesn’t exist. I...
0
by: nima | last post by:
Hi I have two tables in my database. PRODUCT and PRODUCT_GROUP. PRODUCT has a foreign key to PRODUCT_GROUP. I generated a typed dataset using VS.net and added a relationship between the two...
2
by: Christopher Weaver | last post by:
I'm trying to insert a new Row within an existing Table within an existing DataSet using the following: DataRow NewTaskRow = dsTaskActivities.Tables.NewRow();...
2
by: Paul | last post by:
I'm trying to use the Duwamish 7.0 Framework to make a distributed application. I re-wrote parts of the application to work with Students instead of Customers. So instead of a unique email...
5
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data objects to do all this. My only problem now is...
16
by: Geoff Jones | last post by:
Hi Can anybody help me with the following, hopefully simple, question? I have a table which I've connected to a dataset. I wish to add a new column to the beginning of the table and to fill...
5
by: Monty M. | last post by:
Hello; I was wondering if anyone can assist me with this problem. Here are the tools I am using: Language: C# Database: MS SQL Server 2000 Application: Visual Studio 2005 1. I have a...
0
by: BostonNole | last post by:
Is there a way to create an index (non-unique) constraint on a DataTable column? Here is some code sample I would like to run, but the constraint can not be unique. Dim IDDataColumn As...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.