Constraint :unique => true

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

On 16 Jan 2008, at 09:41, ke han wrote:

That will do it. Mysql will enforce uniqueness.

Fred

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.

Looking at the source it rails is ignoring options it doesn’t recognise.