Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Creating MSSQL2008 Tables. Dropping constrains and then recreating the same tables again using Perl

by blackadder (Hermit)
on Dec 03, 2009 at 16:56 UTC ( [id://810874] : perlquestion . print w/replies, xml ) Need Help??

blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Revisiting this thread 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!