SQL Server Performance Forum – Threads Archive

Dropping foreign key constraint.

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.

Run sp_helpconstraint on both the tables and output. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Got any triggers on your tables?
Tom Pullen
DBA, Oxfam GB

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

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

To check triggers run SP_HELPTRIGGERS. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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

Can you post full DDL of both tables? Frank
http://www.insidesql.de
http://www.familienzirkus.de

…forget it [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

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.

Take a look at DROP TRIGGER or ALTER TRIGGER Frank
http://www.insidesql.de
http://www.familienzirkus.de

Use DROP TRIGGER _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Thanks a lot for all your help!

haha satya, beat you by almost a minute. getting old or tired [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

Ah.. not at all… it takes on my net settings at office. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>