Morning Lovely Monks
I have two tables as follows:
@Clusters = qw/ Cluster_ID INT IDENTITY(1,1) PRIMARY KEY, Cluster_N
+ame VARCHAR(50) not NULL unique, DC_ID INT not NULL CONSTRAINT Cluste
+r_2_DC FOREIGN KEY REFERENCES dbo.Data_Centers(DC_ID)/;
@Hosts = qw/Host_ID INT IDENTITY(1,1) PRIMARY KEY, Host_Name NVARCHAR(
+50) NULL, Cluster_ID int CONSTRAINT Host_2_Cluster FOREIGN KEY REFERE
+NCES dbo.Clusters(Cluster_ID), DC_ID int not NULL CONSTRAINT Host_2_D
+C FOREIGN KEY REFERENCES dbo.Data_Centers(DC_ID)/;
and I have the following sub that I intend to use to populate the table.
sub sql_write
{
my ($input, $id, $type) = @_;
if ($type eq 'SRV')
{
printf "SERVER HOST: %s\n", $input->{name};
printf "ID DC: %s\n", $id->{DC};
printf "Cluster ID: %s\n", $id->{Cluster};
# insert data into Hosts table
+
$dbh->do("INSERT INTO Hosts (Host_Name,Cluster_ID,DC_ID) VALUE
+S ('$input->{name}','$id->{Cluster}','$id->{DC}')");
}
}
I call the above sub with this command
my $rec_id = &sql_write($host_data,$Ids,"srv")
All hosts must belong to a data center, but in the same time not all hosts are part of a cluster!
so before I call the sub I do the following to the $Ids
$Ids->{DC} = $dc_id;
$Ids->{Cluster} = $cluster_id;
If do have a value for a cluster then $ids->{Cluster} will have that value, but if I don't have a value for a cluster then I don't want to insert a value to the Cluster_ID, in other words I want the field to remain as "NULL"! However no matter what I have tried since yesterday I kept getting this error!
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver][SQL Serve
+r]The INSERT statement conflicted with the FOREIGN KEY cons
raint "Host_2_Cluster". The conflict occurred in database "DEV1111", t
+able "dbo.Clusters", column 'Cluster_ID'. (SQL-23000) [er
was 1 now 1] [state was 23000 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
+terminated. (SQL-01000) at U:\Doccuments\test.pl line 104, <DATA> lin
+e 1.
Could kindly an enlightened monks advise where I am going wrong or how can I insert a Null value into a field that has been declared as "NULL" in an MSSQL2008 db please? Thanks