使用SMO如何删除主键？

[tylike]

如题：
当一个主键存在时，我使用了Index.Drop();
结果报错如下：
An explicit DROP INDEX is not allowed on index 'dbo.Article.PK_Article'. It is being used for PRIMARY KEY constraint enforcement.

十分感谢！

[tylike]

:(
smo!=sql

[tylike]

Namespace: Microsoft.SqlServer.Management.Smo
Assembly: Microsoft.SqlServer.Smo (in microsoft.sqlserver.smo.dll)

[zxkid]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
Server s = new Server(".\\sqlexpress");

foreach (Table t in s.Databases["test"].Tables)
{
foreach (Index i in t.Indexes)
{
if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
i.Drop();
break;
}
}
}
s.Alter(); //
}
}
}

测试更改成功

[tylike]

楼上仁兄，看到你的回答我非常兴奋。
我也是使用这样的方法，可是报错如下：
会停留类似于：i.Drop()上面。
An explicit DROP INDEX is not allowed on index 'dbo.Article.PK_Article'. It is being used for PRIMARY KEY constraint enforcement.

[tylike]

我把生成的SQL语句拿到了SQL中去运行，报一样的错。
我打了VS2k5的SP2补丁，问题依旧。。。。。

[tylike]

up!

[tylike]

UP!

[fwacky]

alter table [tablename]
nocheck constraint all



alter table [tablename]
check constraint all

[fa_ge]

wangtiecheng(不知不为过，不学就是错！) 兄的正解

[tylike]

不是SQL语句，5555555555555
SMO啊！！！！
Namespace: Microsoft.SqlServer.Management.Smo
Assembly: Microsoft.SqlServer.Smo (in microsoft.sqlserver.smo.dll)

[tylike]

UP！

[yuedeem]

UP！

[tylike]

UP!

[tylike]

可能是版本问题了，我的结果是这个！
/****** Object: Index [PK_uBoxColumn] Script Date: 06/07/2007 17:33:58 ******/
DROP INDEX [PK_uBoxColumn] ON [dbo].[uBoxColumn] WITH ( ONLINE = OFF )
_____________________________________________________________________________
SQL版本：
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework2.0.50727.42
Operating System5.1.2600

[coolpc]

高分 帮顶！

[zxkid]

按理说 i.Drop(); 是不可能出错的
i.Alter()时才可能出错
我机上只有Express版, 不知道其它版结果是否相同

检查你是否有Alter table权限
贴出详细代码

[zxkid]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
Server s = new Server(".\\sqlexpress");
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["test"].Tables)
{
foreach (Index i in t.Indexes)
{
if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
i.Drop();
break;
}
}
}
s.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)
{
richTextBox1.AppendText(st);
}
}
}
}

=====
结果:
USE [test]/****** 对象: Index [PK_user] 脚本日期: 06/07/2007 08:55:54 ******/
ALTER TABLE [dbo].[user] DROP CONSTRAINT [PK_user]

[tylike]

public void SetPrimaryKey(List<PrimaryKeyColumn> columns, bool IsClustered, string memo)
{
UIndex p = PrimaryKey;
//没有建过主键
if (p == null)
{

}
else
{
//Server s = Table.Parent.Parent;
//s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;
_primaryKey.Index.Drop();
//Table.Alter();
//string s1 = "";
//foreach (string st in s.ConnectionContext.CapturedSql.Text)
//{
// s1 += st;
//}
//Debug.Write(s1);
}

_primaryKey = new UIndex(this, PrimaryKeyName);
_primaryKey.Description = memo;
_primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
_primaryKey.IsClustered = IsClustered;
foreach (PrimaryKeyColumn col in columns)
{
_primaryKey.UIndexedColumns.AddNew(new UIndexedColumn(_primaryKey, col.Column, col.IsDesc));
}
_primaryKey.Index.Create();
}

[tylike]

private UIndex _primaryKey;
public UIndex PrimaryKey
{
get
{
if (_primaryKey == null)
{
if (this.UIndexes.Contains(PrimaryKeyName))
{
_primaryKey = this.UIndexes[PrimaryKeyName];
}
else
{
foreach (UIndex idx in this.UIndexes)
{
if (idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
_primaryKey = idx;
break;
}
}
}
}
return _primaryKey;
}
}

private string PrimaryKeyName
{
get
{
return "PK_" + this.Name;
}
}

[tylike]

上面的UIndex是我自己包装的，其实就是原来的Index外包一层
例如：所有以类名U开头均是指加了一下包装，为的是可以扩展属性出来，不要被迷惑：
我重装了SQL，安装了开发版，打了SP1问题依旧。正在下SP2。。。
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;

namespace Admiral.UBox.Core
{
public class UIndex : EntiyBase
{
#region propertyies
UIndexedColumnCollection _uindexedColumns;
public UIndexedColumnCollection UIndexedColumns
{
get
{
if (_uindexedColumns == null)
{
_uindexedColumns = new UIndexedColumnCollection(_index.IndexedColumns);
}
return _uindexedColumns;
}
}

Index _index;
public string Name
{
get { return _index.Name; }
set { _index.Name = value; }
}

public bool IgnoreDuplicateKeys
{
get
{
return _index.IgnoreDuplicateKeys;
}
set
{
_index.IgnoreDuplicateKeys = value;
}
}

public IndexKeyType IndexKeyType
{
get
{
return _index.IndexKeyType;
}
set
{
_index.IndexKeyType = value;
}
}

public bool IsClustered
{
get
{
return _index.IsClustered;
}
set
{
_index.IsClustered = value;
}
}

public bool IsDisabled
{
get
{
return _index.IsDisabled;
}

}

public bool IsFullTextKey
{
get
{
return _index.IsFullTextKey;
}
set
{
_index.IsFullTextKey = value;
}
}

public bool IsIndexOnComputed
{
get
{
return _index.IsIndexOnComputed;
}
}

public bool IsIndexOnTable
{
get
{
return _index.IsIndexOnTable;
}

}

public bool IsPartitioned
{
get
{
return _index.IsPartitioned;
}
}

public bool IsSystemNamed
{
get
{
return _index.IsSystemNamed;
}
}

public bool IsSystemObject
{
get
{
return _index.IsSystemObject;
}
}

public bool IsUnique
{
get
{
return _index.IsUnique;
}
set
{
_index.IsUnique = value;
}
}

public bool IsXmlIndex
{
get
{
return _index.IsXmlIndex;
}
}

public int MaximumDegreeOfParallelism
{
get
{
return _index.MaximumDegreeOfParallelism;
}
set
{
_index.MaximumDegreeOfParallelism = value;
}
}

public string DisplayName
{
get
{
return (string)GetExtPropertyValue("DisplayName");
}
set
{
SetExtPropertyValue("DisplayName", value);
}
}

public string Description
{
get
{
return (string)GetExtPropertyValue("Description");
}
set
{
SetExtPropertyValue("Description", value);
}
}

protected override ExtendedPropertyCollection extPropertyes
{
get { return _index.ExtendedProperties; }
}

internal Index Index
{
get
{
return _index;
}
}

#endregion

internal UIndex(Index index)
{
_index = index;
IsNewObject = false;
}
/// <summary>
/// 新建一个索引
/// </summary>
/// <param name="parent"></param>
/// <param name="Name"></param>
public UIndex(UObject parent, string Name)
{
_index = new Index(parent.Table, Name);
IsNewObject = true;
}

public void Drop()
{
_index.Drop();
}


}
}