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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs