ActiveRecord Unique key association


#1

Hi,
I am in the process of incorporating a legacy database into my first
ruby/rails project. All is going well so far (he says crossing all
toes/fingers!).

I have a User tbl which has a unique key association on two columns,
username and userpassword. So far I have:

class Usertbl < ActiveRecord::Base
set_primary_key “user_id”
end

and I want to add a line after the primary_key declaration, something
like:

set_unique_key “username, userpassword”

How would I do this?


Regards

Andrew


#2

Hi Andrew

You need something along the lines of

validates_uniqueness_of :username, :password

I think

I am still fairly new at this myself!


#3

Hi Rod,

validates_uniqueness_of :username, :password

I think you have it. What I have done is:

validates_uniqueness_of :username,:userpassword, :scope => :user_id

which I believe means, there can only be one username/userpassword
combination for any given user_id? If one of you more knowledgeable
chaps
would like to chime in at this juncture, that would be appreciated :wink:


Regards

Andrew


#4

i don’t see why you would want a unique combination of username AND
password. just a unique key on username should suffice. if you’ve
got one on both, that would allow two users could have the same
username with different passwords and which would satisfy the unique
restriction, and that is something you don’t want.

ie,

username unique => good
(username, password) unique => bad

ex:

mysql> create table testusers (
-> id int auto_increment,
-> username varchar(20),
-> password varchar(20),
-> primary key(id),
-> unique username_password (username, password));

so i have a unique index on the username AND password combination.

mysql> insert into testusers values (’’, ‘bob’, ‘bobpw1’);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> insert into testusers values (’’, ‘bob’, ‘bobpw2’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from testusers;
±—±---------±---------+
| id | username | password |
±—±---------±---------+
| 1 | bob | bobpw1 |
| 2 | bob | bobpw2 |
±—±---------±---------+
2 rows in set (0.00 sec)

now, if i just make username unique by itself:

mysql> insert into testusers values (’’, ‘bob’, ‘bobpw1’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into testusers values (’’, ‘bob’, ‘bobpw2’);
ERROR 1062 (23000): Duplicate entry ‘bob’ for key 2


#5

Hi Chris,

username unique => good

(username, password) unique => bad

Yes, you are correct! So noted and amended. Thanks.


Regards

Andrew