When inserting to a database I find it more convenient to catch a key conflict than to perform a select in order to check for duplicates. Defining a column as unique will ensure data integrity at the lowest level.

SQL
1
2
3
4
5
6
7
8
CREATE TABLE customer (
id INT AUTO_INCREMENT, 
name VARCHAR(20), 
address VARCHAR(50), 
email VARCHAR(50), 
CONSTRAINT PRIMARY KEY(id), 
UNIQUE(email)
);
create table customer (
id int auto_increment, 
name varchar(20), 
address varchar(50), 
email varchar(50), 
constraint primary key(id), 
unique(email)
);

Catching a user attempting to register with an email address which is already in the database is fairly simple when using DBI:

Perl
1
2
my $row = $dbh->do(qq{INSERT INTO customer ( name, address, email ) VALUES (?,?,?);}, undef, ("Rob", "Rob's House", "my@email.com")); # Returns undef on error.
&error($dbh->errstr) unless $row; # Forward to error handler. 
my $row = $dbh->do(qq{INSERT INTO customer ( name, address, email ) VALUES (?,?,?);}, undef, ("Rob", "Rob's House", "my@email.com")); # Returns undef on error.
&error($dbh->errstr) unless $row; # Forward to error handler. 

Class::DBI is a database abstraction Perl module. It wasn’t immediately apparent to me how to handle a key conflict when using it. After a little investigation I found key violations will trigger an exception which makes it fairly simple to handle:

Perl
1
2
3
4
eval
{
        $new_cust = Customer->insert({name=>"Rob", address=>"Rob's House", email=>"my@email.com" }); # Insert using Class::DBI constructor.
}; &error($@) if $@; # Forward to error handler.
eval
{
        $new_cust = Customer->insert({name=>"Rob", address=>"Rob's House", email=>"my@email.com" }); # Insert using Class::DBI constructor.
}; &error($@) if $@; # Forward to error handler.

The eval block will catch the thrown exception, allowing you to gracefully handle it through your own subroutine.