sql server programming: This is my 3rd posting on the subject.

I'm trying to delete a Primary Key in a SQL database. Seems that there is a
constraint blocking the deletion. I can't find any way to delete the key or
the constraint?

The following code works for all normal, unconstrained indexes:

Imports smoIdx = Microsoft.SqlServer.Management.Smo.Index

Dim oidx As smoIdx = otbl.Indexes("PK_Customer")

oidx.Drop()

And help would be appreciated.


Larry Rebich

Hello Larry,

I assume you meant to DROP the primary key(PK) column.

May be some foreign key(FK) is referencing this primary key. If so, you need
to first delete that FK constraint then the PK constraint before proceeding
with the PK column deletion.

You can find out the dependent constraints by using these T_SQL commands:
sp_helpconstraint <table_name
sp_help <table_name


Regards,
Dinesh

[quoted text, click to view]

Larry,

I noticed in your previous post on this subject you specified:

[quoted text, click to view]

In SMO, you can use the Script method to generate the needed Transact-SQL
like the examples below. Note that you will need to address dependencies
(foreign keys) before executing the drop script.

'VB.Net
Dim server As New Server("MyServer")
Dim database As Database = server.Databases("MyDatabase")
Dim t As Table = database.Tables("MyTable")
Dim i As Index = t.Indexes("PK_MyTable")
Dim so As ScriptingOptions = New ScriptingOptions()
so.ScriptDrops = True
Dim dropScript As StringCollection = i.Script(so)
database.ExecuteNonQuery(dropScript, ExecutionTypes.Default)


//C#
Server server = new Server(@"MyServer");
Database database = server.Databases["MyDatabase"];
Table t = database.Tables["MyTable"];
Index i = t.Indexes["PK_MyTable"];
ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
StringCollection dropScript = i.Script(so);
database.ExecuteNonQuery(dropScript, ExecutionTypes.Default);


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]