Design dilemma: If e-mail address already used, send e-mail "e-mail address already registered", but can't because can't add duplicate to table

Question

A registration form asks for username and e-mail address. After the data passes validation it's added to the accounts table. It stores data such as username, password and e-mail address. If the username has already been taken the user will be notified and no data will be added to the table. It was seen as a security issue if the user was immediately notified that the e-mail address was already in the table. The suggested solution to this was to always send a verification e-mail and if the entered username already existed the e-mail would say "this e-mail address has already been used" (and no activation link would be given of course).

The problem is that as it works now, if the INSERT query fails to insert the data into the table the message "Username taken" is shown. This may be wrong because the e-mail address is set to unique in the table so the query fails if the same e-mail address is entered. I can no longer send out a verification e-mail saying "this e-mail address has already been used" because there is no record in the table containing said e-mail address.

How can I redesign the system so it works?

I'm using MySQL and the table accounts has the primary key username unique key e-mail and the attributes password and activation.

if(mysqli_stmt_execute($createAccount))
    echo 'Username available!';
else
    echo 'Username unavailable!';

In SQL is there some way to check to see why the query couldn't be inserted into the table? For example could it tell which attribute had a duplicate value?

Please let me know if my question is unclear.

Answer 1

Run a SELECT query first to identify duplicates. If no duplicates are found, then you can perform your INSERT.

Answer 2

if you want the DB to perform the required check while inserting the data and you want to be able to distinguish between a failed attempt to add an email and a failed attempt to add a username, the most straightforward solution is to have both an email table and an usernametable. Your actual account table would just keep foreign key to those two tables. Here is an example http://sqlfiddle.com/#!2/a24df:

CREATE TABLE username (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, value CHAR(80) UNIQUE NOT NULL);
CREATE TABLE email (id  INT AUTO_INCREMENT PRIMARY KEY NOT NULL, value CHAR(80) UNIQUE NOT NULL);

CREATE TABLE account(id  INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
                     username_id  INT NOT NULL,
                     email_id INT NOT NULL,
                     FOREIGN KEY (username_id) REFERENCES username(id),
                     FOREIGN KEY (email_id) REFERENCES email(id) );

Adding a new user will be now performed as several steps in a transaction. You will now be able to know which step failed if any. And being in a transaction, that will conserve the atomicity of the account creation:

START TRANSACTION;
INSERT INTO email(value) VALUES ("[email protected]");
INSERT INTO username(value) VALUES ("Sylvain");

-- you could obtain the ID programmatically by "last_insert_id"-like function 
INSERT INTO account(username_id, email_id) VALUES (1,1); 
COMMIT;

And querying username and email for a given account will now require a join:

SELECT username.value AS username, email.value AS email 
FROM username JOIN account ON username.id = account.username_id
JOIN email ON email.id = account.email_id
WHERE account.id = 1;

I wouldn't say this is necessary the best solution but it works as you wish, I think.