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)
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”
hello.. i am a begineer and i want to know more about sql and mysql.. what’s major difference between them and if anyone can help me in giving some tutorials on sql.
Hi Pinal Sir,
Thanks for “How to drop foreign key Constraints”.
Plz tell me how to insert default value through default constraint.I have created table below:
Create Table Product
(
PID INT CONSTRAINT T PRIMARY KEY(PID,PNAME),
PNAME NCHAR(25),
PPRICE INT,
PCOLOR NCHAR(8) CONSTRAINT C CHECK(PCOLOR IN(‘WHITE’,'BLACK’,'RED’,'BLUE’,'GREEN’,'VOILET’,'YELLOW’,'ORANGE’))CONSTRAINT C1 DEFAULT ‘WHITE’
)
But I m unable to insert the default value.
Anshu Verma
Just omit the default column in the INSERT statement and dont pass value to it
I have a very large table (37.5 million rows) and am currently dropping the Primary Key as it wasn’t well designed. I would have expected this to be a quite rapid operation but it has been running now for 20 minutes with no sign of finishing.
Is it going to reorganise all the data in the table because of me dropping the Primary Key? I assume this is what it is doing as I can’t think of any reason why else it would take so long to just drop a constraint.
Hi,
when i run a select * from table1 it gives me a error
”
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
”
But when i select the first column like select A from table1 it displays the first column. Also count gives the total count of the rows.
But when u do Select * or apart from first column ‘A’ any column i get the above error.
Whats been done with no success….
DBCC checktable …same above error
DBCC Checkdb …same above error
Started DB in emergency mode and tried
Created a new db and tried to copy the data but could not copy…may be i was doing something wrong. But corrupt table schema got copied but not the data.
deleted the primary and also droped the index.
Possibly a index corrupt issue. When i try sysindexes i get the same error same.
Since the one column can be read …i m pretty optimistic that the data could be recovered
Any help will be sincerely appreciated.
DBCC checktable error….
Msg 8967, Level 16, State 218, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 262144 (type Unknown), page (1:135), row 57. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 293 and 37.
Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:135) with latch type SH. Check statement terminated due to unrepairable error.
Hey Richard,
Did you get answer to your query? I am in a similar scenario, although in my case, the size is many times more – about 200 million records. Please let me know if you figured out what is taking so long. Our process has been running for more than 6 hours.
Also, what would happen if I stop this running operation – note that this drop constraint statement itself is NOT explicitly enclosed in a BEGIN TRAN – END TRAN block.
Thanks,
Manoj Deshpande.
ya i got answer from this page. thank you sir
Is it possible to drop UNIQUE key constraint in SQL Server? i was not able to do so…
consider oscommerce address book table. address has customer_id as MUL (ie, PRI and FK) properties and address_book_id as primary.
By mistake, auto_increment was not associated to any key. I want to make the address_book_id as auto_increament. Yes, i am able to do it. But the problem is existing values still have duplicates. How can i make it have auto_increamented values. Other option i found is, Drop the primary key from address_book. which is dropping both the fields. At the end i want address_book_id field to have auto_increament values. Please suggest
Hi all,
Why no one using sp_helpconstraint ‘Ownername.tablename’.
It will give u list all indexes on a table along with primary index.
Hi all,
Why no one using sp_helpconstraint ‘Ownername.tablename’.
It will give u list all constraints on a table along with primary key constraint.
Hi Dave,
I have a query where I need to create a column say Status.
It is a Not-Null column. The table I m gonna add this column, already have some data in it.
So, wen creating the column I gave a Default value as 1.
Now after addition of this column, in the same script file, I dropped the default constraint. It throws like ‘Status’ column not found.
Please help me out
create table raman
(
Name varchar(10) primary key,
Occupation varchar(10)
);
i use above code in sql server to implement primary key in table
now i have to delete it than which code should be written to delete it
i got it
get the name of constraint from following query
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
than use following query
alter table raman
drop constraint name_of_constraint\
which you get from above query
You just save me from long time trouble here man…thanks and thumbs up
HI sir i want to ask one thing related to Primary key constraints . The question is can be drop a primary key without Primary key name with script. for i.e..if a create a PK one a column without a Primary key name so can be drop it with script?????
Use this for removing Foreign Key…
IF EXISTS (Select XTYPE,object_name (parent_obj) ObjectName, name from sysobjects where xtype like ‘F’
and parent_obj = (object_id(”))
Alter Table DROP Constraint
Sorry, I missed something in my last comment…
IF EXISTS (Select XTYPE,object_name (parent_obj) ObjectName, name from sysobjects where xtype =’F’
and parent_obj = (object_id(‘_Table Name_’))
Alter Table ‘_Table Name_’ DROP Constraint ‘_FK_Key_Constraint Name_’
hello,
i am using sql server 2008,
i have foreign keys in all the tables of my database ..and i get an error while entering data into any of the table.
what should i do?
also i have not added constraints in my tables..are they necessary?
i am unable to drop the primary key using the above syntax..
please help.