Sunday, June 9, 2013

Constraint can neither be added nor deleted - Conflict that constraint already exists


       Few days back, I got into this crazy haunting situation. There was issue with some user who had gone berserk and went on adding some hellish values into the db tables (*, # and so on). Needless to say, that application had crashed completely. Logically after finding the issue cause, first solution was to delete the invalid data. But since foreign key constraints were already applied on the tables, it was not allowing deletion the values - so thought of an alternative workaround

1. Delete Constraints

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

2. Delete Invalid Data

3. Add Constraints back.

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

No problem in step 1 and step 2 ,
But while executing query for adding back the constraints we were dumbstruck.

It was telling that the constraint already exists and was conflicting with the existing.

No worry, I thought, lets rerun the delete constraint query:-

And there was error again telling the constraint doesn't exists :O :O :O

Now, was the time to freak out :P

Tried Changing the constraint name (Dumb move :P ) - Obviously no luck , since the relationship was there,

Try Goggling - little Luck

Got two solutions, but before them the real issue.

Real Issue: - there was still invalid data present. For e.g. in our case, entries were in child table and not in parent .( Yes I know, unbelievable ,I still say the guy who screwed these should be given a Nobel Prize before taking action against him :P) . So the constraint was not getting satisfied and hence was not getting applied.

Solution 1 :- (Temporary Fix - Not REAL fix)

Using WITH NOCHECK - in this case, it will straight away apply the constraint without checking whether existing data satisfy the constraint or not. Invalid data will be still there, and someone by chance fires query on that invalid data - screwed again. Thus, not an ideal way, to be used as a last resort.

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders WITH NOCHECK
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Solution 2 :- (Recommended)

Search for invalid records using not in query - records in child table which are not in parent table or anything data inconsistency between the two tables and delete them.

Hope this helps someone

Happy Coding Querying !!!