Post reply

Need to drop/remove/delete foreign key

  • July 14, 2002 at 11:53 pm

    #58307

    Some objects have been dropped from the sysobjects table using query analyser.

    This has left some foreign keys without a parent or linking table and so is coursing all sorts of errors.

    Can any please give me an idea on how to remove this record, I can’t re-create the object because it has all ready been re-created but with a different ID

    Cheers

    Symon

    Edited by - sfilet on 07/14/2002 11:53:56 PM

  • July 15, 2002 at 7:33 am

    #432693

    First are you sure it was deleted from the sysobjects table. This is not normally possible unless you reconfigure the server to allow system tables to be updated. I ask because they may be enforced via trigger which you can run

    DROP TRIGGER

    against those to drop them. If it is a Constrint use

    ALTER TABLE tblName DROP constName

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 15, 2002 at 5:34 pm

    #432755

    Thank you for the reply

    The server was reconfigured, not sure how (before my time here), SQL’s like this have been run

    Delete from sysobjects where name = 'FK_HRdtg_DeptTraining_HRprs_Person'

    I have fix a few error by recreating the foreign key object and then removing it through a diagram, but some objects have been recreated with different Id’s, so not being able to edit the foreign key table I can’t change the old ID’s to the new ID’s

    I hope this gives you a better understanding of the issues.

    So another questions, how do I lock the sysobjects table so it can’t happen again.

    Also are there any ideas on how to fix these “lost” keys?

    Thank you for your time

    Symon

  • July 15, 2002 at 9:06 pm

    #432769

    Running this should shut down those changes if they are open.

    EXEC master.dbo.sp_configure 'allow updates', '0' RECONFIGURE WITH OVERRIDE

    If the previous options are not fixing the issue you may need to create a new database with the strutures, triggers, etc. you want to keep. Then copy the data into the new database. Rename the old db and then rename the new db the old name. Finally then verify everything is working properly.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 15, 2002 at 11:35 pm

    #432772

    Thank you for your help

    Cheers

    Symon

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply