Unique Constraints the Hard Way

Background

We have plugs with electricity meters attached. We know which meter it is by the unique serial number. We've had issues where field techs accidentally copy down the same serial number for two different plugs, which gums up our database.

As a result, I constructed the database to disallow duplicates of the serial number.

Problem

I also set the plugs to be "paranoid", meaning they have a deleted_at column instead of actually being deleted from the database; we don't want to orphan or relational data.

What if we decommission a plug, but want to reuse the electricity meter? If we try to add it to the database again, even though it has been "deleted", it still exists...violating the unique constraint.

Solution

First off all, get rid of the constraint:

ALTER TABLE <table name> 
DROP CONSTRAINT <constraint name>;

Now, when you create a plug, use this logic.

Plug.findOrCreate( { where: { serial_number: # }, defaults: { data } } )
.spread(function( plug, created ) {
    if ( created ) {
        // no constraint error
    } else {
      // serial number already in use!!!
    }
});

There's a magic trick hidden here. Even though my WHERE only includes the serial number, Sequelize makes the query WHERE ("plug"."deleted_at" IS NULL AND "plug"."serial_number" = '2345'). It automatically filters out deleted plugs and only looks for current unique contraints.