Revisiting this thread http://www.perlmonks.com/?node_id=806517 and some…
Dear Gods, I declare my SQL2008 tables as follows:
#! c:/perl/bin/perl.exe
#
#
use strict;
use Win32::AdminMisc;
use Math::BigInt;
use Data::Dumper;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Time::localtime;
use DBI;
use DBD::ODBC;
use VMware::VILib;
use VMware::VIRuntime;
use vars qw/$Storage $tables $dbh $DB/;
undef $_ foreach ([$Storage, $tables, $dbh, $DB]);
@{$tables->{SAN_Devices}}=qw/SAN_ID INT IDENTITY(1,1) PRIMARY KEY,
SAN_Name NVARCHAR(255) not NULL unique,
SAN_Type NVARCHAR(25),
Notes text/;
@{$tables->{SAN_LUNs}}=qw/LUN_ID INT IDENTITY(1,1) PRIMARY KEY,
LUN_Name NVARCHAR(255) not NULL,
fkSAN_ID INT CONSTRAINT Relation1 FOREIGN KEY
REFERENCES dbo.SAN_Devices(SAN_ID)
ON DELETE CASCADE /;
+
@{$tables->{SAN_WWN}}=qw/WWN_ID INT IDENTITY(1,1) PRIMARY KEY,
WWN_Port NVARCHAR(255) not NULL,
fkLUN_ID INT CONSTRAINT Relation2 FOREIGN KEY
REFERENCES dbo.SAN_LUNs(LUN_ID)
ON DELETE CASCADE /;
And then by using this sub I create, drop constraints and recreate the tables again.
sub sql_prepare
{
my ($tbl) = @_;
print "\n\n\tPreparing Table: $tbl\n\n";
$dbh->do("IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = objec
+t_id(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
my $temp = $dbh->selectcol_arrayref("SELECT constraint_name FROM I
+NFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '$tbl'")
+;
foreach my $rel (@$temp){
next unless ($rel=~/Relation/);
print "\n\t\tFORIEGN KEY: $rel\n";
$dbh->do("ALTER TABLE $tbl DROP CONSTRAINT $rel"); }
$dbh->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id
+(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[$tbl];
CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})"); }
print "\nPreparing DataBase tables:\n";
&sql_prepare('SAN_Devices');
&sql_prepare('SAN_LUNs');
&sql_prepare('SAN_WWN');
If I run the script for the first time on empty database, it works and I get the following output :-)
Preparing DataBase tables:
Preparing Table: SAN_Devices
Preparing Table: SAN_LUNs
FORIEGN KEY: Relation1
Preparing Table: SAN_WWN
FORIEGN KEY: Relation2
Then, I run it again for the second time immediatly, and I get the same out out, so all is well.
But when I ran it for the third time, I get this output:
Preparing CAPMAN DataBase tables:
Preparing Table: SAN_Devices
Preparing Table: SAN_LUNs
Preparing Table: SAN_WWN
FORIEGN KEY: Relation2
Clearly , it failed to print Relationship1 which also means it did not detecet and delete it!
I further checked the db manually using the management studio and found that SAN_LUNs contained both relationship one and two instead of the whats declared in the table which is only Reationship1!! so it added extra constraint!
Can a divine being enlighten me on what’s causing this and how to resolve please!
Thanks