You must Sign In to post a response.
  • Category: SQL Server

    Remove Primary key and add again

    I have primary key field in a table which has foreign key related to 3 more table. I need to update one record which was deleted with old field value. It is auto increment as well.
    Question: Can I drop primary and foreign keys , add the record and again set primary and foreign keys? This need to do in production. Will this create any issue? What steps I need to take?

  • #764754
    1. Take the backup of the database for safety purpose.
    2. Remove the Primary key and foreign key relations of the particular tables.
    3. Add/Delete/update the records. But make sure reference of the values are matching.
    4. Now you can set the primary and foreign keys for the particular table

    By Nathan
    Direction is important than speed

  • #764761

    Before drop the relationship you must and should take backup first then follow below steps.

    1) delete child table records and then parent table records
    2) delete relation between tables and then create your own relations
    3) insert records as you want.

    As per my understanding you want to remove relation and create new relation rather than breakup the existing one, my suggestion is create new table with new relationships and insert new records for existing records insert existing table information into new table.

    Keep the old table as a backup file and for new records insertion use new table.

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #764766
    Take the back up of your DB in your development environment and test it thoroughly to make sure nothing breaks.

    Then keep the back up handy to restore at any time and do the change in production.

    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #764775
    As it is production database, back is must before doing any task.
    As far as your task is concern Yes. The only way would be to drop the constraint with an Alter table then recreate it.
    ALTER TABLE <Table_Name>
    DROP CONSTRAINT <constraint_name>

    ALTER TABLE <Table_Name>
    ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)
    But the problem is For big datasets it can cause a long run time and thus - table in availability.

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764907
    Hai Swatz,
    It's not safe in general but if required, you need to check no one is accessing the database as it will get problem if someone is already using the database.
    Drop the foreign key constrain first and then add the records and then drop the primary key and then reference it again.
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #765884

    Alter Table <TableName> dropContraint <ConstraintName>

    Alter Table Tablename add Constraint ConstraintName

    Once Drop the Primary Key u have add duplicate values in your table after that u cant able to add Primary Key again because your table records duplicate values.

    hope this will help..

  • #765986

    Although, doing this tasks in the production setup is a big risk but if you have other option then, you have to do certain tasks.But while performing the tasks, make sure no operation is taking place in between o the database otherwise there will be a possibility of having inconsistent data in the tables.First of all please take a backup of the DB from production setup.Secondly, remove the Foreign Key Reference from the child table and then remove the Primary Key Reference from the parent table.Then, you can proceed for your operation i.e. inserting/updating/deleting of the records by keeping in mind the reference value should be same otherwise, it will create issue in future after adding the references manually.Lastly, you can add the Primary Key and Foreign Key reference in parent table and child table respectively.

Sign In to post your comments