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 (http://blog.SQLAuthority.com)
pinaldave
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 ??
@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
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.