Prevent entire row duplicates with standard SQL

Lets say we have a very simple SQL table organized as follows:

  email_address | phone_number
  test@test.com | 5555555555
  test@test.com | 9999999999
  user@user.com | 7468382678

In this example, it is perfectly OK to have multiple phone numbers attached to an email address. If I want to prevent insertion of exact duplicates (prevent insertion if both columns match), is there a way to do this using a single query?

(I'd like to avoid the obvious, "SELECT count(*) FROM db WHERE email_address='test@test.com' AND phone_number='5555555555', followed by an INSERT only if we don't find records)

I'm using a standard version of MySql, so proprietary extensions will not work in this case. (I'm aware of IF NOT EXISTS and other MS extensions)

Is there any sort of multi-column condition that I c开发者_JS百科an set up to make this happen? What is the most efficient way to do this?

Using two queries just feels wrong.

Many Thanks.

One way:

create table contact_info
(
  email_address varchar(256) not null ,
  phone_number  varchar(10)  not null ,

  primary key clustered ( email_address , phone_number ) ,

)

You won't be inserting any exact duplicates. B^)

Proactively, you should be able to do something like:

insert contact_info
select *
from ( select <email-addr_value>   as email ,
              <phone-number_value> as phone
     ) t1
where not exists ( select * from contact_info t2
                   where t2.email_address = t1.email
                     and t2.phone_number  = t2.phone
                 )

No, there is no ANSI-SQL statement I am aware of. Try this MySQL statement instead:

INSERT IGNORE INTO your_table (emai_address, phone_number) VALUES ("test@test.com", 5555555555);

Note: You must have a unique index spanning both your columns for this to work:

ALTER TABLE your_table ADD UNIQUE KEY (email_address, phone_number);

Set up a unique constraint on the table based on the two columns, so the insert fails on duplicates.

An alternative ("old school"?) approach to the "alter table" statement in the accepted answer is to create a unique index thusly:

create unique index all_columns_idx on your_table(email_address, phone_number)

Maybe you can try DISTINCT if you only want to deal with the display?

Something like:

SELECT DISTINCT * FROM db