Prevent inserting duplicate records

Question

For the sake of learning I have created two identical tables in SQLite and was trying to figure out how to prevent inserting duplicates from one table to another. I am using this query in trying to find out if duplicates exist before posting the record. However, it does not work right since duplicates still get inserted and no warning message appears.

procedure TForm1.Button1Click(Sender: TObject);
begin
UNIQuery1.Close;
UNIQuery1.SQL.Clear;
UNIQuery1.SQL.Text:='SELECT * FROM TEMP2 WHERE DATE=:F1 AND user=:F2';
UNIQuery1.Params.ParamByName('F1').Value:=UNITable1.FieldByName('DATE').Value;
UNIQuery1.Params.ParamByName('F2').Value:=UNITable1.FieldByName('USER').Value;
UNIQuery1.Open;
if UNIQuery1.isempty then begin  
UNIQuery1.Close;
UNIQuery1.SQL.Clear;
UNIQuery1.SQL.Text:='INSERT INTO TEMP2 (DATE,USER) select DATE,USER FROM TEMP1';
UNIQuery1.ExecSQL;
UNITable2.Refresh;
end
else
ShowMessage('Record already exists !');
end;

Can someone enlighten me on how to do this right ? Table has only 3 fields : ID (Autoinc),DATE(date) and USER (char).Both are identical. So basically I want the program to tell me that the USER and the DATE already exist in the table I am trying to post identical records.

EDIT ; Using Sir Rufos query weird things happen:

Answer 1

SQLite has a built in function to prevent violating the constraints.

ON CONFLICT

Build a unique constraint on the fields Date and User and you can insert the new values with

insert or ignore into TEMP2 ( Date, User )
select Date, User from TEMP1

But it seems that SQLite did not get the uniqueness if one of the fields contains the NULL value.

To check if the the target table contains the values (containing NULL or not) you have to

SELECT * 
FROM TEMP2 
WHERE 
  COALESCE( "DATE", '0000-00-00 00:00:00' ) = COALESCE( :DATE, '0000-00-00 00:00:00' ) 
AND 
  COALESCE( "USER", '' ) = COALESCE( :USER, '' )

---

UPDATE

Your approach will not work, because you only check the current row from TEMP1 but insert all rows from it into TEMP2.

procedure TForm1.Button1Click(Sender: TObject);
begin
  // Prepare the queries

  // check duplicates query
  UNIQuery1.Close;
  UNIQuery1.SQL.Text := 'SELECT * FROM TEMP2 WHERE COALESCE( "DATE", '0000-00-00 00:00:00' ) = COALESCE( :DATE, '0000-00-00 00:00:00' ) AND COALESCE( "USER", '' ) = COALESCE( :USER, '' )';

  // insert data query
  UNIQuery2.Close;
  UNIQuery2.SQL.Text := 'INSERT INTO TEMP2 (DATE,USER) VALUES (:DATE,:USER)';

  // walk through TEMP1
  UNITable1.First;
  while not UNITable1.EOF do
  begin
    // check current row of TEMP1 for duplicates
    UNIQuery1.Params.ParamByName('DATE').Value := UNITable1.FieldByName('DATE').Value;
    UNIQuery1.Params.ParamByName('USER').Value := UNITable1.FieldByName('USER').Value;
    UNIQuery1.Open;
    // if no duplicates found
    if UNIQuery1.IsEmpty then 
    begin
      // insert the data
      UNIQuery2.Params.ParamByName('DATE').Value := UNITable1.FieldByName('DATE').Value;
      UNIQuery2.Params.ParamByName('USER').Value := UNITable1.FieldByName('USER').Value;
      UNIQuery2.ExecSQL;
      // delete current entry from TEMP1
      UNITable1.Delete;
    end
    else
      // next row from TEMP1
      UNITable1.Next;
  end;
  // refresh
  UNITable1.Refresh;
  UNITable2.Refresh;
end;

But for this you have to be careful in multi-user-scenarios. Someone can insert the same data in the small time gap between checking this soft constraint and inserting the data.

And this are hard to find failures