.Net SQL Server Bug – Optional Foreign Keys

pts.
Tags:
Development
Has anyone experienced a bug in .Net on SQL Server databases when you have a NULLable column that has a foreign key constraint on it. What is happening to us is that when you try to insert a row in a table with a NULL in a column that has a foreign key constraint on it, we get a foreign key constraint violation error. This does not happen with .Net and Oracle. The work arounds are to: 1) drop the foreign key constraint and guarantee your program code enforces the RI. Problem with this is that you can't use cascading deletes. 2) Or, insert a dummy row in the parent table with a 0 or ' ' in its primary key, then when inserting rows in the child table, put a 0 or ' ' in foreign key column when it is logically null. Problem with this is that you when querying or reporting on the parent table you have to remember to filter out the dummy row. Any advice? Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I don’t get your problem. See my test code below.

private SqlConnection mDbConnection;
private SqlConnection DbConnection
{
get
{
if(this.mDbConnection == null)
{
this.mDbConnection = new SqlConnection(@”Persist Security Info=False;Integrated Security=SSPI;database=tempdb;server=(local)”);
this.mDbConnection.Open();
}
return this.mDbConnection;
}
}

private void dropMasterTable()
{
string lCommandText = @”DROP TABLE MasterTable”;

try
{
SqlCommand lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();
}
catch
{
// do nothing
}
}

private void dropChildTable()
{
string lCommandText = @”DROP TABLE ChildTable”;

try
{
SqlCommand lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();
}
catch
{
// do nothing
}
}

private void createMasterTable()
{
string lCommandText = @”CREATE TABLE dbo.MasterTable
(
Oid varchar(50) NOT NULL,
Number int NULL,
Description varchar(50) NULL
) ON [PRIMARY]

“;

SqlCommand lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();

lCommandText = @” ALTER TABLE dbo.MasterTable ADD CONSTRAINT
PK_MasterTable PRIMARY KEY CLUSTERED
(
Oid
) ON [PRIMARY]
“;
lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();
}

private void createChildTable()
{
string lCommandText = @”CREATE TABLE dbo.ChildTable
(
Oid varchar(50) NOT NULL,
Number int NULL,
Description varchar(50) NULL,
MasterTableOid varchar(50) NULL
) ON [PRIMARY]
“;

SqlCommand lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();

lCommandText = @” ALTER TABLE dbo.ChildTable ADD CONSTRAINT
PK_ChildTable PRIMARY KEY CLUSTERED
(
Oid
) ON [PRIMARY]
“;
lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();

lCommandText = @”ALTER TABLE dbo.ChildTable ADD CONSTRAINT
FK_ChildTable_MasterTable FOREIGN KEY
(
MasterTableOid
) REFERENCES dbo.MasterTable
(
Oid
)”;
lCommand = new SqlCommand(lCommandText, this.DbConnection);
lCommand.ExecuteNonQuery();

}

DataSet mDataSet;
private void createDataSet()
{
// create dataset
mDataSet = new DataSet(“myDataSet”);
SqlDataAdapter lAdapter = new SqlDataAdapter(“SELECT * FROM MasterTable WHERE 0=1″, this.mDbConnection);
lAdapter.Fill(mDataSet, “MasterTable”);
lAdapter = new SqlDataAdapter(“SELECT * FROM ChildTable WHERE 0=1″, this.mDbConnection);
lAdapter.Fill(mDataSet, “ChildTable”);

// set prim key
DataColumn [] lPrimaryKey = new DataColumn[1];
lPrimaryKey[0] = mDataSet.Tables[”MasterTable”].Columns[”Oid”];
mDataSet.Tables[”MasterTable”].PrimaryKey = lPrimaryKey;

lPrimaryKey = new DataColumn[1];
lPrimaryKey[0] = mDataSet.Tables[”ChildTable”].Columns[”Oid”];
mDataSet.Tables[”ChildTable”].PrimaryKey = lPrimaryKey;

// set foreign key
mDataSet.Relations.Add(mDataSet.Tables[”MasterTable”].Columns[”Oid”], mDataSet.Tables[”ChildTable”].Columns[”MasterTableOid”]);
}

private const string MasterOid1 = “MasterOid1″;
private void fillParent()
{
DataRow lRow = this.mDataSet.Tables[”MasterTable”].NewRow();
lRow[”Oid”] = MasterOid1;
lRow[”Number”] = 1;
lRow[”Description”] = “Masterrecord 1″;
this.mDataSet.Tables[”MasterTable”].Rows.Add(lRow);
}

private const string ChildOid1 = “ChildOid1″;
private const string ChildOid2 = “ChildOid2″;
private const string ChildOid3 = “ChildOid3″;
private const string ChildOid4 = “ChildOid4″;
private void fillChild()
{
DataRow lRow = this.mDataSet.Tables[”ChildTable”].NewRow();
lRow[”Oid”] = ChildOid1;
lRow[”Number”] = 1;
lRow[”Description”] = “Childrecord 1″;
lRow[”MasterTableOid”] = MasterOid1;
this.mDataSet.Tables[”ChildTable”].Rows.Add(lRow);

lRow = this.mDataSet.Tables[”ChildTable”].NewRow();
lRow[”Oid”] = ChildOid2;
lRow[”Number”] = 2;
lRow[”Description”] = “Childrecord 2″;
this.mDataSet.Tables[”ChildTable”].Rows.Add(lRow);

lRow = this.mDataSet.Tables[”ChildTable”].NewRow();
lRow[”Oid”] = ChildOid3;
lRow[”Number”] = 3;
lRow[”Description”] = “Childrecord 3″;
lRow[”MasterTableOid”] = DBNull.Value;
this.mDataSet.Tables[”ChildTable”].Rows.Add(lRow);

try
{
lRow = this.mDataSet.Tables[”ChildTable”].NewRow();
lRow[”Oid”] = ChildOid4;
lRow[”Number”] = 4;
lRow[”Description”] = “Childrecord 4″;
lRow[”MasterTableOid”] = “Not existing FK”;
this.mDataSet.Tables[”ChildTable”].Rows.Add(lRow);

throw new Exception(“FK is invalid”);
}
catch
{
// should fail
}

}

private void updateDb()
{
SqlDataAdapter lAdapter = new SqlDataAdapter(“SELECT * FROM MasterTable”, this.DbConnection);
SqlCommandBuilder lCommandBuilder = new SqlCommandBuilder(lAdapter);
lCommandBuilder.RefreshSchema();

lAdapter.Update(this.mDataSet, “MasterTable”);

lAdapter = new SqlDataAdapter(“SELECT * FROM ChildTable”, this.DbConnection);
lCommandBuilder = new SqlCommandBuilder(lAdapter);
lCommandBuilder.RefreshSchema();

lAdapter.Update(this.mDataSet, “ChildTable”);

}

private void testForeignKey()
{
this.dropChildTable();
this.dropMasterTable();
this.createMasterTable();
this.createChildTable();
this.createDataSet();
this.fillParent();
this.fillChild();

this.updateDb();
}

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following