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 /;
[download]

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');
[download]

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
[download]

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
[download]

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