SQL Server Performance

Dropping foreign key constraint.

Discussion in 'T-SQL Performance Tuning for Developers' started by kannanonline, Nov 11, 2003.

  1. kannanonline New Member

    I have two tables (master and child).relation between the two tables is they are linked by a column employeeid.I have records in these two tables.

    Now i have removed the foreign key constraint from the child table by using this st/

    Alter table Emp_Status drop constraint Emp_FK01

    When i did this,it showed constraint dropped successfully.

    Now when i try to insert records into the child table, i get the following error:

    The record can't be added or changed. Referential integrity rules require a related record in table 'EmpMaster'.

    I gave sp_help emp_status , and saw no constraints,i also checked this query,

    select * from sysobjects where xtype='F', the constraint was not there.

    Still i could not add records to the child table.Any help will be appreciated.
  2. satya Moderator

    Run sp_helpconstraint on both the tables and output.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. thomas New Member

    Got any triggers on your tables?




    Tom Pullen
    DBA, Oxfam GB
  4. kannanonline New Member

    How to check that?Bcos the DB was created and used by anothet team.
  5. kannanonline New Member

    I did run sp_helpconstraint, and it showed primary key constraint for master table and no constraints for child table.
  6. satya Moderator

    To check triggers run SP_HELPTRIGGERS.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. kannanonline New Member

    Great,i found out,there are triggers written which prevents inserting..thanks a lot for your immediate help.
  8. FrankKalis Moderator

  9. FrankKalis Moderator

    ...forget it [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  10. kannanonline New Member

    One more Q?How do i drop that insert trigger alone which is fired when inserting.I mean i need to remove the relation of the trigger with the table.
  11. FrankKalis Moderator

  12. satya Moderator

    Use DROP TRIGGER

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. kannanonline New Member

    Thanks a lot for all your help!
  14. FrankKalis Moderator

    haha satya, beat you by almost a minute. getting old or tired [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  15. satya Moderator

    Ah.. not at all... it takes on my net settings at office.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page