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 !!!

Saturday, November 3, 2012

Design View tab not visible in Visual Studio for User Control

Issue : Design View tab not visible in Visual Studio for User Control.

Alternate Issue Descriptions :
  • Design View,Split,Markup tabs all absent for User Control.
  • Visual Studio showing all asp controls invalid for User Control.
  • No ASP.NET/AJAX tools shown in toolbox for User Control.

Story : I was working on a User Control for one of my webpage. It was working all perfectly. But after two three days, when I tried to edit it, voila, all the three tabs for navigating to design view or even split view were all gone. I tried right click to show designer, it gave me message with "Designer file already open.Do you want to close it" => Yes. After that, next thing made me pull my hair, all asp tags were in green, design page was fill with error creating control (even basic label and text box).Even Toolbox didn't had any of the tools except basic HTML.

Solution : The Solution was simple. I was using a JavaScript function in a script tag.The Script tag was just floating midway between my user control declarations (ajax n all) at the start and my main div (full of elements). As any normal webpage, the script tag should be enclosed inside html head tag . It sounds easy, but it really drove me and people around me crazy. Initially, Visual Studio makes us believe that it is not necessary to include that script tag inside head, but after 4-5 builds, one day you check the user control, to find it is showing only markup, no sign of three tabs at the bottom. Hope this helps someone.


Code Sample Snippet

<html>
<head>
<script> 
 //your JavaScript code ,functions
</script>
</head>
</html>


Hello World !!!

   Time to start with my second blog. Unlike my first blog, subject and purpose of this blog is specific to Coding. Not like a tutorial or I know everything kinda blog , this blog is just like a log report or better - a fixed error log report.
    It happens almost daily, in office or at home, we start working on something. But rarely, the code runs smoothly on first run itself. Rest time , there are errors and exceptions which make our life hell. We then go crazy over Google searching for a solution. Some solutions are easily done by our own logic , some require help from our colleagues , some are easily found online courtesy Google (Copy Paste FTW  !!! ), while some are deadly which consist of fusion of all the above. The end result is a solution which we use for the time being .But after some days, some other guy gets stuck and you fail to recollect it. The search begins again. 
   With the help of this blog, I will try to post various programs I was stuck on, various errors which I got caught in and then came up with a solution. Hopefully, it will help the needy person, saving time and finally coming with better bug free code.