.Net SQL Server Bug – Optional Foreign Keys
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!
Software/Hardware used:
Software/Hardware used:
ASKED:
June 23, 2005 1:17 PM
UPDATED:
June 24, 2005 2:44 PM
Answer Wiki:
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();
}
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
