• Welcome, Guest. Please login.
 
August 26, 2019, 12:52:32 am

News:

Welcome to the SQLitening support forums!


Really Preventing Duplicate Records

Started by Fredrick Ughimi, December 03, 2017, 05:40:20 pm

Previous topic - Next topic

Fredrick Ughimi

December 03, 2017, 05:40:20 pm Last Edit: December 03, 2017, 05:59:34 pm by Fredrick Ughimi
Hello,

I noticed recently in one of my applications running in a Client/Server mode, users managed to enter duplicate values for fields I had already defined as unique (HospitalNo and PhoneNo). I guess I am doing something wrong. Here is an insight:


slOpen ("HospitalProDB.db3", "C")

slExe Build$("Create Table If Not Exists tblBioData(HospitalNo TEXT COLLATE NOCASE, Registration TEXT, Title TEXT, Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE,", _
"DateCreated TEXT, PhoneNo TEXT, Email TEXT, HomeAddress TEXT, OfficeAddress TEXT, Company TEXT, Occupation TEXT, Sex TEXT, Age INTEGER, AgeClass TEXT,", _
"MaritalStatus TEXT, BirthDate TEXT, Religion TEXT, StateOrigin TEXT, Tribe TEXT, EnroleeNo TEXT, Category TEXT COLLATE NOCASE, Plan TEXT, Entitlements TEXT, EnroleeExpiry TEXT,", _
"HMOName TEXT, LGA TEXT, BirthPlace TEXT, Nationality TEXT, PixName TEXT, KinName TEXT, KinPhone TEXT, KinEmail TEXT, KinAddress TEXT, Relationship TEXT, Username TEXT, Picture BLOB)")

slExe "Create UNIQUE Index If Not Exists BioDatandx1 ON tblBioData(HospitalNo, PhoneNo)"
     
slExe "Create Index If Not Exists BioDatandx2 ON tblBioData(Surname, Othernames, DateCreated)"

'Save Routine:
Errorcode& = slExeBind(slBuildInsertOrUpdate("tblBioData", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), _
      slBuildBindDat(sHospitalNo, "T") & _
      slBuildBindDat(sRegistration, "I") & _
      slBuildBindDat(sTitle, "T") & _
      slBuildBindDat(sSurname, "T") & _
      slBuildBindDat(sOthernames, "T") & _     
      slBuildBindDat(SQLiteDate(sDateCreated), "T") & _     
      slBuildBindDat(sPhoneNo, "T") & _     
      slBuildBindDat(sEmailAddress, "T") & _     
      slBuildBindDat(sHomeAddress, "T") & _ 
      slBuildBindDat(sOfficeAddress, "T") & _ 
      slBuildBindDat(sCompany, "T") & _ 
      slBuildBindDat(sOccupation, "T") & _ 
      slBuildBindDat(sSex, "T") & _ 
      slBuildBindDat(sAge, "T") & _ 
      slBuildBindDat(sAgeClass, "T") & _ 
      slBuildBindDat(sMaritalStatus, "T") & _ 
      slBuildBindDat(SQLiteDate(sBirthDate), "T") & _ 
      slBuildBindDat(sReligion, "T") & _ 
      slBuildBindDat(sStateOrigin, "T") & _ 
      slBuildBindDat(sTribe, "T") & _
      slBuildBindDat(sEnroleeNo, "T") & _ 
      slBuildBindDat(sCategory, "T") & _ 
      slBuildBindDat(sPlan, "T") & _
      slBuildBindDat(sEntitlements, "T") & _ 
      slBuildBindDat(sEnroleeExpiry, "T") & _ 
      slBuildBindDat(sHMOName, "T") & _ 
      slBuildBindDat(sLGA, "T") & _
      slBuildBindDat(sBirthPlace, "T") & _
      slBuildBindDat(sNationality, "T") & _
      slBuildBindDat(sPixName, "T") & _
      slBuildBindDat(sKinPhone, "T") & _
      slBuildBindDat(sKinEmail, "T") & _
      slBuildBindDat(sKinAddress, "T" ) & _
      slBuildBindDat(sKinName, "T") & _
      slBuildBindDat(sRelationship, "T") & _
      slBuildBindDat(gUsername, "T") & _                             
      slBuildBindDat(sPicture, ("B")),"E") 

   If Errorcode& = 19 Then
      MsgBox "HospitalNo Already Exists", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title 
      Control Set Focus hfrmBioData1, %ID_FRMBIODATA1_TXTHOSPITALNO
      Exit Method
   End If
     


Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Combined index c1,c2 needs index for c1 and c2

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
slSetProcessMods "E1"
slOpen "sample.db3"
slexe "drop table if exists t1
slexe "create table if not exists t1(c1,c2)"
slexe "create unique index if not exists c1_Plus_c2 on t1(c1,c2)"
slexe "insert into t1 values(1,1)"
slexe "insert into t1 values(1,2)" 'duplicate 1, not reported
slexe "insert into t1 values(3,4)"
slexe "insert into t1 values(5,4)" 'duplicate 4, not reported
? "No problem if combined index, now use separate indexes"

slexe "drop table if exists t1"
slexe "create table if not exists t1(c1,c2)"
slexe "create unique index if not exists c1 on t1(c1)"
slexe "create unique index if not exists c2 on t1(c2)"

slexe "insert into t1 values(1,1)"
slexe "insert into t1 values(1,2)" '1 reported as duplicate
slexe "insert into t1 values(3,4)"
slexe "insert into t1 values(5,4)" '4 reported as duplicate
END FUNCTION                           

Bern Ertl

As a matter of style and code maintenance, I would prefer to have any UNIQUE restrictions defined in the CREATE TABLE declaration itself.

create table if not exists t1(c1 UNIQUE, c2 UNIQUE, UNIQUE( c1,c2) )

The above is a bit redundant, but you can specify whether values in c1 are unique, values in c2 are unique and/or c1,c2 pairings are unique all within the CREATE TABLE statement.

cj

December 04, 2017, 05:04:24 pm #3 Last Edit: December 04, 2017, 05:10:15 pm by cj
I like it too, but the ability to drop the index, do things and then add the index back again has advantages.
Of course this can be done with the UNIQUE defined within the create statement, but might have to create another table and copy back.

Hope Fredrick comes up with a good way to add a primary key instead of just compound indexes.
slExe "Create UNIQUE Index If Not Exists BioDatandx1 ON tblBioData(HospitalNo, PhoneNo)"
slExe "Create Index If Not Exists BioDatandx2 ON tblBioData(Surname, Othernames, DateCreated)"