DataTable primary key - unique and no nulls by default?

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

Guest

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

HTH

Elton Wang
 
D

deko

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

Guest

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

deko

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;

???
 
G

Guest

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

Patrice

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

newbie: why does DataSet AutoIncrement advance by 2? 8
How to retrieve TimeSpan from typed DataSet? 1
How is System.Data.DataTable.Load() determining whether a column is a primary key? 1
strange exception with datatable 2
Unique Ke Violation 3
primary key - concatenated or autonumber? 6
Cannot remove Unique key from Typed DataTable. 1
Setting the Primary key of a DataTable 1

Top