Subject | Re: [IBO] dublicate records |
---|---|
Author | Gediminas |
Post date | 2003-09-11T07:32:01Z |
In my case there would be such personal info as name, surname, gender,
location ( required fields + optional fields as country, ID & etc). Every
record will have PK, so rely on PK wouldn't be enough.
I thought, that TIB_Query would have method for dublicate column
recognition, but I'll try unique constrait (required + optional fields)
At 2003.09.11 01:49, you wrote:
The Truth Is Out There
location ( required fields + optional fields as country, ID & etc). Every
record will have PK, so rely on PK wouldn't be enough.
I thought, that TIB_Query would have method for dublicate column
recognition, but I'll try unique constrait (required + optional fields)
At 2003.09.11 01:49, you wrote:
>At 12:13 AM 11/09/2003 +0200, you wrote:--/ Gediminas /--
> >What is the best way to check for dublicate records? I want to have a form,
> >where user could enter pair's info - if members of pair doesn't exist, they
> >are saved in DB; if such record already exist, posting to DB is prevented.
>
>Using dataset methods, the only way to find out whether a new record is a
>duplicate is to
>1) design your table so that it protects itself from duplicates (use a
>unique constraint if the primary key is not sufficient)
>and then
>2) try to post the record and trap the uniqueness violation exception.
>
>In SQL, the way to find out whether there is a duplicate in the *current
>transaction's view of database state*, is to test whether a record already
>exists containing the uniqueness condition, e.g.
>
>select 1 from TheTable
>where UniqueColumn = :uniquecolumn
>
>In a stored procedure, you can do
>
>if (exists (select 1 from TheTable where UniqueColumn = :uniquecolumn)) then
> <perform update OR exit>;
>else
> <perform insert>
>
>But another transaction could create a duplicate after your test...so it's
>generally safest to let table constraints and exceptions take care of
>existence testing - meaning you have to Post before you can find out
>whether there will be a uniqueness violation.
The Truth Is Out There