DataSet DataTable Unique constraint

[Brian Keating]

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

[Nicholas Paldino [.NET/C# MVP]]

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

[Jay B. Harlow [MVP - Outlook]]

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

[Brian Keating]

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

[Brian Keating]

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

[Jay B. Harlow [MVP - Outlook]]

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
| >
| >
| >

[Brian Keating]

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
| >
| >
| >

[Brian Keating]

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