I'm asking the opposite of the question to force empty strings to be NULL; instead, I want fields that are empty strings to be stored as empty strings. The reason I want to do so (even in contradiction to what some people say) is that I want to have a partial uniqueness constraint on the table that works for multiple database types (postgres, mysql, etc), as described in this question here.
The psuedocode for the schema is basically:
Person {
first_name : String, presence: true
middle_name : String, presence: true
last_name : String, presence: true
birth_date : String, presence: true
city_of_birth: String, presence: true
active: tinyint
}
The constraint is that a person must be unique if they are active; inactive people can be not unique (ie, I can have multiple John Smiths that are not active, but only one active John Smith).
Further complication: According to the project specification, only first_name and last_name are required to be given by the user, all other fields can be blank.
Our current solution for applying the partial uniqueness constraint is to use the fact that NULL != NULL, and set the active tinyint to NULL if someone is not active and set it to 1 if they are active. Thus, we can use this rails code in the migration:
add_index :Persons, [first_name, middle_name, last_name, birth_date,
city_of_birth, active], unique:true, name: "unique_person_constraint"
In order for this constraint to work, however, none of the other fields can be NULL. If they are, then two John Smiths with no other filled in fields and active = 1 will still be 'unique' because the middle_name fields with value NULL will be different from each other (because NULL != NULL, regardless of column type).
However, when I do
options = { first_name: "John",
middle_name: "",
last_name: "Smith",
birth_date: "",
city_of_birth: "",
}
person = Person.new(options)
success = person.valid?
success is always false because
Middle name can't be blank
City of birth can't be blank
Birth date can't be blank
So I need a way to ensure that I always have at least empty strings for those other fields to enforce that partial uniqueness constraint. How can I do this? If I get rid of the presence:true in the model definition, then it appears that NULL fields are now allowed, which is bad.
This is Rails 3.2.13, and I can give other gems and gem versions if necessary.
