I have a migration called CreateUsers.
in “self.up” I add the following columns, among others:
t.column :display_name, :string, :limit => 26, :null =>
false, :unique => true
t.column :email, :string, :unique => true
I’m not sure where I came up with the idea that :unique => true could
be used as a column constraint; but this is what I did. My DB is
MySQL 5.0.x and I receive no errors when I rails runs the migration to
create the table.
I thought I had a table structure and behavior from ActiveRecord that
would ensure a record could not be inserted with duplicate email
address. I was wrong. My error was uncovered under Rails 1.2.3 and I
see the same behavior on Rails 2.0.2.
A friend tells me that in order to create a unique column constraint I
need to add an index as such:
add_index :users, :email, :unique => true
Wouldn’t this just give me an index of unique email address? It
wouldn’t actually cause MySQL to throw an error on an insert with a
duplicate email address, would it?
Keep in mind, this is what I’m look for: I want an error to be
returned by MySQL if a user record is inserted which contains a
duplicate email address.
Another friend tells me that part of the problem is MySQL. That MySQL
won’t manage such a constraint for me and I should look to PostgreSQL
for such behavior.
This is ok. I don’t mind switching DBs if I get the behavior I’m
looking for.
So this leaves me with the following questions:
1 - Is “unique => true” a valid constraint for a column definition
assuming the DB supports it? If not, why do I not receive an error
during the migration? If so, shouldn’t it throw an error for such DB
targets that don’t support it? btw, I have tried this against
Postgresql 8.3 and I don’t see any such constraint created in my users
table. It appears rails is silently ignoring the error.
2 - Is the method described above of adding an index the “rails way”
to solve this problem? If so, can someone point me to a tutorial or
docs on this way of thinking.
thanks, Jon