How To Delete One More Primary Keys in SQL?

Question

i trying delete all primary keys one table on SQL. If i had one primary key, i can deleted this code line:

ALTER TABLE dbo.TblName DROP CONSTRAINT PK_TblName;

But, if i have one more primary keys, i take this error:

Msg 3728, Level 16, State 1, Line 18 'PK_TblBooks' is not a constraint. Msg 3727, Level 16, State 0, Line 18 Could not drop constraint. See previous errors.

And i try this on C# but they arent deleted:

String sql= "SELECT COL_NAME(ic.object_id,ic.column_id) AS ColumnName FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id WHERE i.is_primary_key = 1 AND ic.object_id = i.object_id";
SqlCommand cmd= new SqlCommand(sql, con);
SqlDataAdapter adp= new SqlDataAdapter(cmd);
DataTable dtsk = new DataTable();
adp.Fill(dtsk);

for (int i = 0; i < dtsk.Rows.Count; i++)
{
String remove= "ALTER TABLE " + Table + " DROP CONSTRAINT PK_" + Table + "";
SqlCommand cmd1= new SqlCommand(remove, con);
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
}

How can watchin way for solve this problem?

Double check the name of the constraint, you probably just typed it in wrong. Or are you executing the same statement more than once? Each constraint has it's own unique name. In the above statement the name for that constraint is `PK_TblName` — Igor, Aug 10 '18 at 13:21
@Igor It's name i wanna deleted all primary keys in table name. — , Aug 10 '18 at 13:24
`It's name i wanna deleted all primary keys in table name` <= I do not know what you mean by this (sorry). There is no drop statement for all constraints on the table. Run **1 drop statement per constraint** using the constraint name. — Igor, Aug 10 '18 at 13:27
Please show us what you are seeing that makes you think there is a Primary Key named `PK_TblBooks` on your `name` table. You are misinterpreting it, whatever it is. — Tab Alleman, Aug 10 '18 at 13:57
@Callout It's not 100% clear, but if both those fields are defined as primary key fields on the same table, then no that's one primary key made up of multiple _columns_ - otherwise known as a compound primary key. There can only be one primary key in a single table. But anyway the error message isn't about that...you seem to be trying to delete something which doesn't exist. — ADyson, Aug 10 '18 at 14:12
@Callout that's not showing us, that's telling us. Post a screenshot or I don't believe you. — Tab Alleman, Aug 10 '18 at 17:21

score 1 · Answer 1 · answered Aug 10 '18 at 14:00

Try using dynamic sql to delete some table's pk.

declare @sql nvarchar(max) = '';
with CTE as (
  select T1.*,T2.name tableName from sys.key_constraints T1
  left join sys.objects T2 on T1.parent_object_id = T2.object_id
  where T2.name = 'T' --your table name
)
select @sql = concat(@sql,'ALTER table ',tableName,' DROP CONSTRAINT ',name,';')
from CTE;

exec(@sql); --delete T table all pk 

select * from sys.key_constraints; --result:null

TEST DDL:

CREATE TABLE T
    ([col1] varchar(2), [col2] varchar(2)
       ,constraint PK_TblName unique (col1)
       ,constraint PK_TblName2 unique (col1)
    )
;

How To Delete One More Primary Keys in SQL?

1 Answers1

TEST DDL:

online demo