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
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.
thanx sir its really workfull
Hi Pinal,
I want to delete primary key data in one table with out deleting foreign key data in some other table. Is there any way to do this instead of dropping both primary and foreign key constraints on both tables.