We write about Ruby on Rails, React.js, React Native, remote work, open source, engineering and design.
Here are some of the issues we noticed while migrating to SQL Server.
As per the ANSI SQL standard unique constraint should allow multiple NULL values.
PostgreSQL documentation on unique constraint states following.
In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.
In SQL Server a unique constraint does not allow multiple NULL values.
Devise by default adds unique index on
add_index :users, :reset_password_token, :unique => true
Devise is doing the right thing by enforcing a unique index on
reset_password_token so that when a user clicks on a link to reset
password the application would know who the user is.
However here is the problem. If we add a new user then by default the
NULL. If we add another user then we
have two records with
NULL value in
reset_password_token. This works
But SQL Server would not allow to have two records with
So how do we solve this problem.
add_index :users, :reset_password_token, unique: true, where: 'reset_password_token IS NOT NULL'
Please visit this issue if you want to see detailed discussion on this topic.
This behavior of SQL Server comes in play in various forms.
Let's say that we are adding
api_auth_token to an existing users
Typically a migration for that might look like as shown below.
add_column :users, :api_auth_token, :string, :null => true, :unique => true
In this case we have plenty of records in the
users table so the above
migration will fail in PostgreSQL. We will have to resort to usage
partial index to fix this issue.
In PostgreSQL following case will work just fine.
add_column :users, :email, :string, :unique => true change_column :users, :email, :null => false
Above migration will fail with SQL Server.
In SQL Server a "not null constraint" cannot be added on a column which has a index on it. We need to first remove the unique index, then add the "not null" constraint and then add the unique index back.
The other solution is to add
not NULL constraint first in the
migration and then add any index.
ActiveRecord supports Array Datatype for PostgreSQL. We were using this feature to store a list of IDs.
After switching to SQL server we converted the column into string type and serialized the array.
serialize :user_ids, Array