The Software Purist |

Nov/15

30

SQL-related Registration Bug and the Importance of Duplicate Data

So, I ran into a subtle registration bug in the development of Compucator.com, so it became an interesting opportunity to talk about some of the technical details.

As you probably are aware, I’m very much against the typical copy-paste programming we often see during development. Most likely, if you need to copy-paste something, probably it should be refactored. That being said, sometimes it’s a useful tool. The problem with doing such is that it’s also an error-prone tool.

The situation is that I was adding fields to a table in the SQL database. It was a table related to user information, so there were fields, such as address, zip code, phone number, email, etc…

Some of the fields were meant to be marked, as UNIQUE. For example, you might argue that email should be unique to prevent duplicate registrations. Obviously, the account name must be unique. However, it seems when I was doing this I accidentally made a copy-paste error and applied the UNIQUE attribute to the zip code field.

This is not the actual code, but imagine it looks like this:


CREATE TABLE Details
(
...
name VARCHAR(100) UNIQUE NOT NULL,
...
zipCode VARCHAR(10) UNIQUE NOT NULL
...
);

The real issue here is that because the zipCode was set as UNIQUE, when two users registered with the same zip, the second one failed and was not able to register. Going through the logs, I realized this occurred once, so I had to fix it.

Theoretically, I should’ve caught this during my unit tests. Unfortunately, I missed having any test cases where there were duplicate zip codes which is how it made it all the way to production, so definitely lesson learned here.

Furthermore, I wanted to correct this programmatically, as there were multiple unique constraints on this table. I didn’t want to take any risks of deleting the wrong constraint, and this change needed to make it to my SQL update scripts so any other test databases received the same update. The code would normally look something like:

ALTER TABLE Details
DROP CONSTRAINT UQ_Details_zipCode;

However, SQL Server will automatically generate names for constraints that I don’t explicitly give names to. So, the names were things like UQ_Details_xxxxxxxxxxx, where the name was unfortunately not meaningful.

Unfortunately, this still proved to be some complicated SQL code, so it seems the easiest thing to do was something like the following:


DECLARE @command nvarchar(1024)
DECLARE @tableName nvarchar(256) = N'Details'
DECLARE @columnName nvarchar(256) = N'zipCode'

SELECT @command = 'ALTER TABLE ' + OBJECT_NAME(so.parent_obj) + ' DROP CONSTRAINT ' + so.name
FROM sysobjects so
WHERE OBJECT_NAME(so.parent_obj) = @tableName
AND so.xtype = 'UQ';

print @command;

--execute (@Command)

NB: I tend to print out the command to make sure it’s doing what I think before I execute it.

I then just added a few lines to the script to re-add the constraints for fields that were actually supposed to have them.

Perhaps there’s a better solution, but this seemed cleanest. Has anyone run into any similar issues? Feel free to share your experiences.

No tags

No comments yet.

Leave a Reply

<<

>>

Theme Design by devolux.nh2.me