One area that always, unfailingly pulls my interest is SQL Server Errors and their solution. I enjoy the challenging task of passing through the maze of error to find a way out with a perfect solution. However, when I received the following error from one of my regular readers, I was a little stumped at first! After some online probing, I figured out that it was actually syntax from MySql and not SQL Server. The reader encountered error when he ran the following query.
ALTERÂ TABLE Table1
DROPÂ PRIMARYÂ KEY
GO
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘PRIMARY’.
As mentioned earlier, this syntax is for MySql, not SQL Server. If you want to drop primary key constraint in SQL Server, run the following query.
ALTERÂ TABLE Table1
DROPÂ CONSTRAINT PK_Table1_Col1
GO
Let us now pursue the complete example. First, we will create a table that has primary key. Next, we will drop the primary key successfully using the correct syntax of SQL Server.
CREATEÂ TABLE Table1(
Col1 INT NOTÂ NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARYÂ KEYÂ CLUSTERED (
Col1 ASC)
)
GO
/* For SQL Server/Oracle/MS ACCESS */
ALTERÂ TABLE Table1
DROPÂ CONSTRAINT PK_Table1_Col1
GO
/* For MySql */
ALTERÂ TABLE Table1
DROPÂ PRIMARYÂ KEY
GO
I hope this example lucidly explains how to drop primary key. This, no doubt, is a very simple and basic explanation, but when I chanced upon the error message it aroused curiosity in me. As you all know by now I love sharing new issues and ideas with my readers. So I have included this interesting error in my blog.
Let me have your feedback on this post and also, do feel free to share with me your ideas as well!
Reference : Pinal Dave (https://blog.sqlauthority.com)
61 Comments. Leave new
Hi Pinal,
I want to drop the primary key constraint from a table.But i don know the constraint name because I haven’t provided the name of the constraint while creating table.
Can u provide me the solution for this ??
Hi!!!
if you want to know the constraint name just violate the primary key by try to insert the same values in table.
Which shows the constraint name.
like this
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘pk_addcons’. Cannot insert duplicate key in object ‘dbo.ADDCONS’.
The statement has been terminated.
here pk_addcons is the constraint name..
You can also know it from the result opf
exec sp_help ‘table_name’
aLTER TABLE tablename
DROP CONSTRAINT constraintname
from the below sql statement use for find the constraintname
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Thank you, this was helpful – I had the constraint name but needed to look up the table to which it applied, and
SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME like ‘%constraint_name%’ did the trick.
@Abheesh
You can find out primary key name for a table if you execute below script
select object_name (parent_obj) ObjectName, name
from sysobjects
where xtype = ‘PK’
and parent_obj = (object_id(‘owner_name.table_name’))
In above script, change owner_name and table_name.
Also, in SQL Server Management Studio, in Object Explorer, if you expand databases, expand tables, expand your table_name, expand key, you will see primary key for that table.
~ IM.
Imran Mohammed’s answer(SQL) has solved my problem.
Thanx
DECLARE @VALUE VARCHAR(52);
SET @VALUE=(select name from sysobjects ………………….)
ALTER TABLE myTable DROP CONSTRAINT @VALUE
———
Msg 102, Level 15, State 1, Procedure DELETECONSTRAINT, Line 8
Incorrect syntax near ‘@VALUE’.
Can u tell me what is the reason for this error?If possible,
can u plz provide me a solution in a single query???
@abheesh
Try this,
DECLARE @VALUE VARCHAR(52);
Declare @Sqlcmd varchar(1000)
SET @VALUE=(select name from sysobjects ………………….)
set @Sqlcmd = ‘ALTER TABLE myTable DROP CONSTRAINT ‘+ @VALUE
Exec (@Sqlcmd)
This should work.
Sir, i want to drop my table having primary key.
What command should i use.
Dharm,
If the table is not participating in a relationship (a foreign key constraint from another table is not referencing the primary key) you can use the DROP TABLE T-SQL command.
ie: DROP TABLE myTable
If the table is participating in a relationship, you will have to either drop the foreign key constraint from any child tables and then you can drop the table. Please note that this will cause your child table to have orphanned records. If the child records does not make sense without the parent record (ie: Order Line does not make sense without an Order Header) then when you are designing your tables you might want to you the CASCASE DELETE table option. This way when you remove a record from the parent the DELETE operation will cascade down to any child tables.
how to drop a primary key constraint for a specific column
ya i got answer from this page. thank you.
In SQL 2000, we perform the following SQL for dropping the primary key constraint:
ALTER TABLE TblRenAudit
DROP CONSTRAINT PK1
GO
and receive the following error:
Msg 7613 Cannot drop index PK1 because it enforces the full-text key for table TblRenAudit.
I’m not finding anything helpful for this.
Any suggestions?
Sir, when I creat a table named producers and define the primary key as producerid and I want to drop the primary key but I encounter error message.My code as below,
create table producers
(producerid nvarchar(4) not null,
producername nvarchar(10) not null,
phone nvarchar(8) not null,
fax nvarchar(8) not null,
membercategory nvarchar(3) not null
primary key(producerid))
alter table producers drop producerid
go
–error message:
Msg 3728, Level 16, State 1, Line 1
‘producerid’ is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Can I drop a non-constraint primary key?Because I don’t know the primary key is constraint or not .By the way ,can you explain the constraint concept?
@abheesh
Another way to get primary key using DMV
select I.name as PrimaryKeyName
from sys.tables T
join sys.indexes I on I.object_id = T.object_id
where T.object_id = object_id(‘SchemaName.TableName’)
and I.is_primary_key = 1
Thanks for the post Pinal.
I used Sung’s method of finding the name of the primary key, and it worked like a charm.
Thanks again.
I have been trying to drop primary key constraint by using “drop index”…
This post guided me to a right direction.
Microsoft usually tries their best to ease the pain of development or maintenance for developers or IT professionals by introducing easier way to do stuff.
But for dropping primary key constraint, IMHO, MySQL wins.
no well this meter
Hi
I have to remove primary key from multiple table.
so I use a following query for it:
ALTER TABLE table_name
DROP CONSTRAINT pk_id;
it is a give a error like:
Msg 3728, Level 16, State 1, Line 1
‘pk_id’ is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
please helm me for this error.
I am using a SQL SERVER 2005 express Edition.
Ankit Gusani.
Hello Ankit,
The error indecates that the primary key name is different than pk_id. Before dropping primary key constraint you will have to drop all foreign key constraints also.
Regards,
Pinal Dave
Hello Pinal Sir,
Thank You For Replying
As You Right that primary key name is different than pk_id but my database not having any foreign key and only one id field that’s name is pk_id.
my database table having following structure
Table Name: Table_Test
Fields Name Datatype
pk_id int
name varchar(50)
address varchar(50)
where I set pk_id is as primary key.
So, Help me to drop the primary key using query.
This is only sample table that I am trying to remove but this same process I have to done in many problem.
So,
Please Reply Me.
Ankit Gusani
Hello Ankit,
Know the primary key name from sp_help. Primary key constraint name would be different than column name.
Regards,
Pinal Dave
Hi Pinal,
I need your help with sql server 2005. I want to insert a running clock in any table where it will auto increment. So once set this clock cannot be changed. Just one row with two columns Time, Current time. Time column is just a ref. Can be an int as well but current time should auto increment every second. I need it to be a clock inside sql.
Any help is sincerely appreciated.
icon_5000
Where should I execute the script?
I tried the script that you have mentioned but getting this error”.Net SqlClient Data Provider: Msg 3728, Level 16, State 1, Line 2
‘PK_XXXXX’ is not a constraint.
.Net SqlClient Data Provider: Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.”
I am sure that a PK constraint is existing on the table.
Hi, your blog is interesting, I came here by looking how to drop a primary unique key, but I have a problem and I don’t know how to fix it. Let me explain:
I had a table named tblPagos with primary unique key column “idPago”
Due to some problems, it was impossible at least to me to make an update using a join from other table, so I decide to rename the table to “tblPagosOld” and create a new tblPagos, so, now I have two tables: tblPagos and tblPagosOld
I made an insert in the new tblPagos by selecting data from tblPagosOld and doing an inner join from a master table
After that I drop the table tblPagosOld
Now I want to create a primary unique key for column “idPago” from the new tblPagos but SQL Server tells me that there’s a duplicate PK_tblPagos and tell me the object id (the old one) and I can’t find where is the reference to that PK
I was taking a look at the database diagram, also I was trying to make a new primary key using a different name, in addition I was looking for it on sys.indexes, sys.contraints, also I was trying to run the script by using “alter table tblPagos” and executing the drop
There’s some option? there’s something I’m not seeing? any help it could be appreciate
ALTER TABLE Table1
DROP PRIMARY KEY
works in Oracle, along with “…DROP CONSTRAINT”