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
My Visual Database → General → 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
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.
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.
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, 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
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'
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;
Thank you very much EHW................................
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
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;
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
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;
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.
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.
My Visual Database → General → How to Make Column Values Unique in Table?
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi