Conditional Relationship?
Jonathan Wood
However, I would like the option of having rows in one of the tables with no
corresponding row in the other table. To that end, I've set the foreign key
field to allow NULLs.
This seemed reasonable but I get an error (The INSERT statement conflicted
with the FOREIGN KEY constraint...) when I set the foreign key to NULL.
Is there no way to have a conditional relationship but still have SQL Server
ensure non-NULL values are valid?
Thanks.
Jon
Tom Cooper
the foreign key allows nulls. Make sure you are inserting NULL into the
child table, not something like the empty string (''). If you give use the
DDL of the tables, primary keys, and foreign key and the INSERT which is
failing, we should be able to point you in the right direction.
Create Table FooParent (ParentID int Primary Key);
Create Table FooChild (ChildID int Primary Key,
ParentID int Null,
Constraint FKChildParent Foreign Key (ParentID) References
FooParent(ParentID));
Insert FooParent(ParentID) Values (1);
Select 'Inserting child with ParentID of 1, should be OK'
Insert FooChild(ChildID, ParentID) Values (1,1);
go
Select 'Inserting child with ParentID of 2, should Fail'
Insert FooChild(ChildID, ParentID) Values (2,2);
go
Select 'Inserting child with ParentID of Null, should be OK'
Insert FooChild(ChildID, ParentID) Values (3,Null);
go
Select 'FooChild should now contain rows with ChildID 1 and 3, but not 2'
Select ChildID, ParentID
From FooChild;
go
Drop Table FooChild;
Drop Table FooParent;
Tom
"Jonathan Wood" <jw...@softcircuits.com> wrote in message
news:uslSReeW...@TK2MSFTNGP04.phx.gbl...
Erland Sommarskog
> However, I would like the option of having rows in one of the tables
> with no corresponding row in the other table. To that end, I've set the
> foreign key field to allow NULLs.
>
> This seemed reasonable but I get an error (The INSERT statement conflicted
> with the FOREIGN KEY constraint...) when I set the foreign key to NULL.
to NULL, but to 0, the empty string or some other non-NULL value.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Jonathan Wood
Thanks, I was thinking the approach was valid.
I'm using ASP.NET/ADO.NET and I'm actually setting the value using an object
that contains DBNull.Value. I admit I've had a bit of confusion surrounding
database nulls but I was thinking I this correct.
Showing my code is a little tricky because it involves a number of custom
routines. But the value being passed for the parameter value is the return
value of the following method, which is accepting an dropdownlist instance
argument:
protected object GetOptionalDropDown(DropDownList ddl)
{
object obj = int.Parse(ddl.SelectedValue);
if ((int)obj == 0)
obj = DBNull.Value;
return obj;
}
protected object GetOptionalTextBox(TextBox tb)
{
object obj = tb.Text;
if (((string)obj).Length == 0)
obj = DBNull.Value;
return obj;
}
--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com
Jonathan Wood
>> However, I would like the option of having rows in one of the tables
>> with no corresponding row in the other table. To that end, I've set the
>> foreign key field to allow NULLs.
>>
>> This seemed reasonable but I get an error (The INSERT statement
>> conflicted
>> with the FOREIGN KEY constraint...) when I set the foreign key to NULL.
>
> Double-check your code. Apparently you are not setting the FK column
> to NULL, but to 0, the empty string or some other non-NULL value.
Thanks, I believe I'm setting it to the .NET DBNull.Value. I thought that
would work.
Jonathan Wood
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CB56EE71...@127.0.0.1...
--
Jonathan Wood
"Tom Cooper" <tomc...@comcast.net> wrote in message
news:#Jg#zRfWKH...@TK2MSFTNGP04.phx.gbl...