fbsd_user said: > create table members ( > logon_id varchar(15) NOT NULL, > email_addr varchar(30) NOT NULL, > member_type char(1), > email_verified char(1), > logon_pw varchar(15), > date_added date, > last_login timestamp, > count_of_logons INT, > first_name varchar(30), > last_name varchar(30), > primary key login_id (login_id), > UNIQUE INDEX email_addr (email_addr)); --->>>>>>>^^^^^^^^^^^^ > When doing a insert row, if the logon_id value is all ready in > the table I get a dup id msg. This is fine and what I want to > happen. > > But when inserting a row with a unique logon_id value that has > a email_addr that is already used by some other logon_id, > mysql allows the insert. This is not the action I want. > > I need to be able to do lookup by logon_id or by email_addr > and retrieve the row. I can do that now, but if 2 logon_id's have > the same email address I get both rows. I need the email address > to be unique across all rows. > > How can I change this table definition so email_addr is unique > across all rows of the table? > > I read the manual about 'unique & index' options, but still don't > comprehend what the manual says. > > Also as you can see I do not select a engine type, is there some > engine type better suited and or faster for the way I am > trying to use the keys? > > Thanks for your advice and help.
Close but you need to specify that the index is UNIQUE (see changes above) ------- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
