DataTable primary key - unique and no nulls by default?

  • Thread starter Thread starter deko
  • Start date Start date
D

deko

When I define a DataTable's primary key, do I need to set it as unique and
to disallow nulls?

project_ID.Unique = true;
project_ID.AllowDBNull = false;

Or is this all that's necessary:

tblProject.PrimaryKey = new DataColumn[] {
tblProject.Columns["project_ID"] };

Should I set just the PK or do I need the other two lines of code as well?
 
Once you define a DataColumn as primary key column, it's Unique becomes true
and AllowDBNull becomes false automatically.

HTH

Elton Wang
 
Once you define a DataColumn as primary key column, it's Unique becomes true
and AllowDBNull becomes false automatically.
Click to expand...

Thanks, I was playing around and this indeed appears to be the case. However, I
am still having problems trying to delete a row from my DataSet.

Here is the DataTable definition:

DataTable tblProject = database.Tables.Add("TableProject");
tblProject.Columns.Add("ProjectName", typeof(string));
[more columns added...]
//primary key definition
DataColumn project_ID = tblProject.Columns.Add("Project_ID", typeof(Int32));
tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns["Project_ID"] };
project_ID.AutoIncrement = true;
project_ID.AutoIncrementSeed = 1;
project_ID.AutoIncrementStep = 1;

Here is how I am trying to delete the row:

[drPrj is a DataRow object]
if (drPrj != null)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}

This results in an apparent key violation - "Project_ID cannot be null"
"TableProject" is the parent table and "TableConfiguration" is the child table.

Here is the FK definition:

DataColumn parentCol =
database.Tables["TableProject"].Columns["Project_ID"];
DataColumn childCol =
database.Tables["TableConfiguration"].Columns["Project_ID"];
ForeignKeyConstraint fkPC =
new ForeignKeyConstraint("ProjectConfigurationFK", parentCol, childCol);
fkPC.DeleteRule = Rule.Cascade;
fkPC.AcceptRejectRule = AcceptRejectRule.Cascade;
database.Tables["TableConfiguration"].Constraints.Add(fkPC);

If you see anything wrong here, please let me know...

Why am I getting that error?

Full text of error:

System.Data.NoNullAllowedException was unhandled
Message="Column 'Project_ID' does not allow nulls."
Source="System.Data"

Thanks in advance.
 
Try

if (drPrj["Project_ID"] != DBNull.Value)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}

HTH

Elton Wang


deko said:
Once you define a DataColumn as primary key column, it's Unique becomes true
and AllowDBNull becomes false automatically.
Click to expand...

Thanks, I was playing around and this indeed appears to be the case. However, I
am still having problems trying to delete a row from my DataSet.

Here is the DataTable definition:

DataTable tblProject = database.Tables.Add("TableProject");
tblProject.Columns.Add("ProjectName", typeof(string));
[more columns added...]
//primary key definition
DataColumn project_ID = tblProject.Columns.Add("Project_ID", typeof(Int32));
tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns["Project_ID"] };
project_ID.AutoIncrement = true;
project_ID.AutoIncrementSeed = 1;
project_ID.AutoIncrementStep = 1;

Here is how I am trying to delete the row:

[drPrj is a DataRow object]
if (drPrj != null)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}

This results in an apparent key violation - "Project_ID cannot be null"
"TableProject" is the parent table and "TableConfiguration" is the child table.

Here is the FK definition:

DataColumn parentCol =
database.Tables["TableProject"].Columns["Project_ID"];
DataColumn childCol =
database.Tables["TableConfiguration"].Columns["Project_ID"];
ForeignKeyConstraint fkPC =
new ForeignKeyConstraint("ProjectConfigurationFK", parentCol, childCol);
fkPC.DeleteRule = Rule.Cascade;
fkPC.AcceptRejectRule = AcceptRejectRule.Cascade;
database.Tables["TableConfiguration"].Constraints.Add(fkPC);

If you see anything wrong here, please let me know...

Why am I getting that error?

Full text of error:

System.Data.NoNullAllowedException was unhandled
Message="Column 'Project_ID' does not allow nulls."
Source="System.Data"

Thanks in advance.
Click to expand...
 
if (drPrj["Project_ID"] != DBNull.Value)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}
Click to expand...

That sounds like good advice. Thanks.

One more question about foreign keys:

When I define the table that will contain a foreign key field, should I disallow
nulls in that field?

For example:

DataTable tblConfiguration = database.Tables.Add("TableConfiguration");
tblConfiguration.Columns.Add("ConfigurationName", typeof(string));
tblConfiguration.Columns.Add("Project_ID", typeof(int)); //foreign key
<<*************
//primary key definition
DataColumn Configuration_ID = tblConfiguration.Columns.Add("Configuration_ID",
typeof(Int32));
tblConfiguration.PrimaryKey = new DataColumn[] {
tblConfiguration.Columns["Configuration_ID"] };
Configuration_ID.AutoIncrement = true;
Configuration_ID.AutoIncrementSeed = -1;
Configuration_ID.AutoIncrementStep = -1;

**********>> Should I add this line of code:

project_ID.AllowDBNull = false;

???
 
Hi deko,

I don't think that system can automatlcally set AllowDBNull to false for
you. Hence, it's better to set it.

HTH

Elton

deko said:
if (drPrj["Project_ID"] != DBNull.Value)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}
Click to expand...

That sounds like good advice. Thanks.

One more question about foreign keys:

When I define the table that will contain a foreign key field, should I disallow
nulls in that field?

For example:

DataTable tblConfiguration = database.Tables.Add("TableConfiguration");
tblConfiguration.Columns.Add("ConfigurationName", typeof(string));
tblConfiguration.Columns.Add("Project_ID", typeof(int)); //foreign key
<<*************
//primary key definition
DataColumn Configuration_ID = tblConfiguration.Columns.Add("Configuration_ID",
typeof(Int32));
tblConfiguration.PrimaryKey = new DataColumn[] {
tblConfiguration.Columns["Configuration_ID"] };
Configuration_ID.AutoIncrement = true;
Configuration_ID.AutoIncrementSeed = -1;
Configuration_ID.AutoIncrementStep = -1;

**********>> Should I add this line of code:

project_ID.AllowDBNull = false;

???
Click to expand...
 
Not done automatically IMO as you could have a nullable/non nullable field
depending on the nature of this relation...

--
Patrice

deko said:
if (drPrj["Project_ID"] != DBNull.Value)
{
drPrj.Delete();
dtPrj.AcceptChanges();
}
Click to expand...

That sounds like good advice. Thanks.

One more question about foreign keys:

When I define the table that will contain a foreign key field, should I disallow
nulls in that field?

For example:

DataTable tblConfiguration = database.Tables.Add("TableConfiguration");
tblConfiguration.Columns.Add("ConfigurationName", typeof(string));
tblConfiguration.Columns.Add("Project_ID", typeof(int)); //foreign key
<<*************
//primary key definition
DataColumn Configuration_ID = tblConfiguration.Columns.Add("Configuration_ID",
typeof(Int32));
tblConfiguration.PrimaryKey = new DataColumn[] {
tblConfiguration.Columns["Configuration_ID"] };
Configuration_ID.AutoIncrement = true;
Configuration_ID.AutoIncrementSeed = -1;
Configuration_ID.AutoIncrementStep = -1;

**********>> Should I add this line of code:

project_ID.AllowDBNull = false;

???
Click to expand...
 
Back
Top