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