1 (edited by kim143 2017-05-01 13:50:57)

Topic: How to Make Column Values Unique in Table?

Hello Community

How I can make values of column Unique in Table

Example

Allowed

a
b
c
d

Not Allowed

a
a
b
b

I mean I don't want duplicate value to be entered in particular column

Re: How to Make Column Values Unique in Table?

Have you searched on this forum? There are some useful resources such as: http://myvisualdatabase.com/forum/viewtopic.php?id=1441

I used this trick a lot with success, maybe other people have found an easier or better way.

Re: How to Make Column Values Unique in Table?

Kim, The example tcoton pointed out is an excellent example, but may be a bit more advanced for a newbie. I don't mean to insult your intelligence. If you understand that particular example, then disregard my example. I put together a very simple project for you to learn the concept.

Post's attachments

Attachment icon Check For Duplicates.zip 581.77 kb, 515 downloads since 2017-05-01 

Re: How to Make Column Values Unique in Table?

Hi EHW,


Nice example for my single cell brain...
What about when checking more than 1 field. Tried the following for two fields dup check but didn't work:

procedure Form2_Button_Save_OnClick (Sender: string; var Cancel: boolean);
begin
    If (SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0) and
       (SqlExecute('Select Count(Id) From MyTable Where Col2 = "' + Form2.Edit_Col2.Text + '"') > 0) then

      Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Form2.Edit_Col2.SetFocus;
         Exit;
      End;
end;
Adam
God... please help me become the person my dog thinks I am.

Re: How to Make Column Values Unique in Table?

Adam, If both columns need to exist ("and condition") then change the If statement to the following:

If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '" and Col2 = "'
      + Form2.Edit_Col2.Text + '"') > 0 then

Re: How to Make Column Values Unique in Table?

Thanks a lot EHW..............


I don't if it's possible to display checked field/s values is/are displayed in error message?
For instance if we are checking for name and surname fields for dup record, instead of 'Duplicate Record is not allowed - Cannot Save'; having  'Peter White exist in the database. Duplicate record is not allowed - Cannot Save'

Adam
God... please help me become the person my dog thinks I am.

Re: How to Make Column Values Unique in Table?

Just substitute your Name and Surname fields where I have Col1 and Col2.

If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '" and Col2 = "'
      + Form2.Edit_Col2.Text + '"') > 0 then
      Begin
         MessageBox(Form2.Edit_Col1.Text + ' ' + Form2.Edit_Col2.Text + ' exist in the database.' + #10#13#10#13 + 'Duplicate record is not allowed - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
      End;

Re: How to Make Column Values Unique in Table?

Thank you very much EHW................................

Adam
God... please help me become the person my dog thinks I am.

Re: How to Make Column Values Unique in Table?

thanks you guys for help I tested few methods like doing with code and doing same check with database.

Which will be good with code or with database.

With database applying unique index to particular column using sqlite studio or with sqlexecute command

like

ex : 

//Run It Only Once

procedure Form1_OnShow (Sender: string; Action: string);
begin
//sqlexecute('PRAGMA foreign_keys=off');

//sqlexecute('BEGIN TRANSACTION');

//sqlexecute('ALTER TABLE products RENAME TO old_products');

//sqlexecute('CREATE TABLE "products" (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, "product_name" TEXT NOT NULL, "quantity" INTEGER NOT NULL DEFAULT 0, "id_product_brand" INTEGER NOT NULL, FOREIGN KEY(id_product_brand) REFERENCES product_brand(id), CONSTRAINT "product_name_unique" UNIQUE (product_name))');


//sqlexecute('INSERT INTO products SELECT * FROM old_products');

//sqlexecute('COMMIT');

//sqlexecute('PRAGMA foreign_keys=on');
end;


or use comparison code in script which will be better

thanks smile

Re: How to Make Column Values Unique in Table?

Kim,  Either way is acceptable. There really is no one right way. The one that works for you is the right way. My personal preference is to just do it through code. But I would still define the index on the database. If you go the database route, you may want to consider intercepting the constraint error message through code. The system generated error messages are not the most user friendly. Users may be confused as to what happened. Dimitry has provided a solution in this forum on how to intercept the SQL exception message and replace it with your own. I keep a folder of helpful scripts from this forum that I can use as a reference when I need it rather than trying to find the topic in the forum. Here is a general script to intercept the SQL constraint message for the Unique constraint and replace it with your own message.

// The following is an example for Unique Constraint error

// global event for all exceptions related database 
function OnSQLException(Sender: TObject; Msg: string; SQL: string): boolean;
begin
    // Exception when you use the functions SQLExecute or SQLQuery

    if Sender=nil  then
    begin
        if Pos('UNIQUE constraint failed', Msg)=1 then
        begin
          result := true; // to prevent system message
          MessageBox('Record already exists', 'Error', MB_OK+MB_ICONWARNING); // your message
        end;
    end;

    // Exception from MVD save operation assigned to a button

    if Sender = Form1.SaveButton then    // if exception from Form1.SaveButton
    begin
        if Pos('UNIQUE constraint failed', Msg)=1 then
        begin
            result := True; // to prevent system message
            MessageBox('Record already exists.', 'Error', MB_OK+MB_ICONWARNING);
        end;
    end;
end;

Re: How to Make Column Values Unique in Table?

EHW,
I found this post from a few years ago and it's what I was looking for.
I have a question, how do I modify it to check for UPPER/lower case letters?
When testing I added abc as data and then I added Abc as data.
It did not flag the 2 records as the same.
I know abc and Abc are technically different sets of data, but when a user is typing in a name, they might not be careful about case sensitivity.
Is there any way to check the data in a situation like this?
Thanks, Frank

12

Re: How to Make Column Values Unique in Table?

A way papafrankc is for the duplicate test to convert all the text to either uppercase or lowercase.

UpperCase(s:string): string;  or LowerCase(s:string): string;

On a clear disk you can seek forever

13 (edited by derek 2021-10-11 10:05:48)

Re: How to Make Column Values Unique in Table?

Hi Frank,
Wouldn't it be easier to prevent it happening in the first place and so negate the need for any additional duplicate testing for different cases?
When you save your record, convert the relevant fields at that point to all lowercase, all uppercase or all namecase (whichever suits best).
It makes the data look tidier too.
Derek.

Post's attachments

Attachment icon forcecase.zip 336.74 kb, 198 downloads since 2021-10-11 

Re: How to Make Column Values Unique in Table?

Frank,
I put together the attached sample project for someone else on the forum. It will catch duplicates for a newrecord or a showrecord. It doesn't matter what case the user types in, it will catch the duplicate. Hope it helps.

Post's attachments

Attachment icon Dup Check.zip 338.99 kb, 209 downloads since 2021-10-12