How to drop a constraint

Question

Using SQL Server 2008

Table1 has a constraint with the name idx_u_customerinfo.

I deleted table1 directly (right click delete), I wrote a script for creating the table1 with same constraint name idx_u_customerinfo.

I'm getting an error

There is already an object named 'idx_u_customerinfo' in the database.

I need to delete the constraint name, How to do this?

`delete` only removes the rows from the table. It does not drop the table. — , Jul 14 '14 at 11:47
What do you get when you run: `SELECT Name, type_desc FROM sys.objects WHERE name = N'idx_u_customerinfo'` ? — marc_s, Jul 14 '14 at 12:05
If you're writing a script to create the table and the index run it for the table part then remove the index, when you drop the table remember to drop the index before the table. — Serpiton, Jul 14 '14 at 12:35

score 0 · Answer 1 · answered Jul 14 '14 at 11:39

0

ALTER TABLE "table_name" DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME";

So like: ALTER TABLE table1 DROP CONSTRAINT idx_u_customerinfo;

answered Jul 14 '14 at 11:39

AriesTiger

69
1
8

I alreay deleted the table, but constraint name is still showing – Gopal Jul 14 '14 at 11:41
The constraint was saved so have you tried creating table1 again just to drop the constraint? – AriesTiger Jul 14 '14 at 18:30

David · Answer 2 · 2014-07-14T19:29:28.920

Either you did not actually drop table1 or the index idx_u_customerinfo exists in another table. You can't have two tables in the same database with the same index name, so it's good practice to incorporate the table name into the index name, e.g. idx_table1_customerinfo.

This should give you all the information about this index to determine what to do next

SELECT tbl.name, idx.name, idx.type_desc
FROM sys.objects idx 
JOIN sys.objects tbl ON idx.parent_object_id = tbl.object_id
WHERE idx.name = N'idx_u_customerinfo'

How to drop a constraint

2 Answers2