SQL SERVER – How to Drop Primary Key Contraint

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)

About these ads

51 thoughts on “SQL SERVER – How to Drop Primary Key Contraint

  1. 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..

    • 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.

  2. @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.

  3. 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???

  4. @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.

  5. 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.

  6. 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?

  7. 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?

  8. @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

  9. 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.

  10. 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.

  11. 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

    • 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

  12. 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.

  13. 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

  14. 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.

  15. 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

  16. 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.

  17. 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.

  18. 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.

  19. 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.

  20. 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

  21. Hi all,

    Why no one using sp_helpconstraint ‘Ownername.tablename’.

    It will give u list all indexes on a table along with primary index.

  22. 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.

  23. 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

  24. 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

  25. 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?????

  26. 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

  27. 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_’

  28. 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.

  29. 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.

  30. Hi sir, consider I have 3 primary keys in a table (sql server). If i drop the primary key constraint, all the primary keys are droped, how i drop only certain primary key.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s