rails-sqlserver/activerecord-sqlserver-adapter — Issue #153: Change UNIQUE constraint to allow multiple NULL values
Author: michelgrootjans
Created: 2011-12-06 09:06:08+00:00
State: open

I am using devise for authentication. One of the mechanisms of devise add a unique index on user.reset_password_token. This allows users to use a reset password link with a one-time token to reset their password. Once that password has been reset, the field "reset_password_token" is reset to null.

This is where SqlServer gets in the way.

According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. SQL Server has always implemented a crippled version of this, allowing a single NULL but disallowing multiple NULL values.

Multiple workarounds have been thought of to fix this behavior. Could it be possible to fix this in a generic way in activerecord-sqlserver-adapter?

see http://resquel.com/ssb/2009/07/22/NULLInUniqueIndexWhereSQLServerGetsItWrong.aspx
see http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values