Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Community » Newbie corner » SQL : how to prevent duplicates ?
SQL : how to prevent duplicates ? [message #42377] Mon, 10 March 2014 20:15 Go to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi,

Sorry for this very newbie question, but either I'm blind, either there is nothing about it : I'm googling for a long time and neither I can find a solution, nor I can get it working...

Let's say that we have this table :

TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40)
   STRING_  (FIRSTNAME, 40)
END_TABLE


We don't want duplicate names on this table. To begin, let's say that the LASTNAME must be unique. How to do that ???

I tried :
TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40) UNIQUE INDEX
   STRING_  (FIRSTNAME, 40)
END_TABLE

but this does not prevents to have duplicates...

In a second time, we will have to autorize duplicate LASTNAMES, but only with different FIRSTNAMES (that is, you can have John SMITH and Jack SMITH, but not two times John SMITH with 2 different CODE). How to do that ?

Sure, it can be done with some simple tests, but normally (almost) all SQL engines can manage that internally with a UNIQUE INDEX, isn't it ? So, there is surely a way to manage that with UPP...

PS : I tried with SQLite, but as far as I know, one can normally create UNIQUE INDEX with this engine ?

[Updated on: Mon, 10 March 2014 20:17]

Report message to a moderator

Re: SQL : how to prevent duplicates ? [message #42378 is a reply to message #42377] Mon, 10 March 2014 21:43 Go to previous messageGo to next message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

Hi jibe,

Unique index can be made simply with UNIQUE:
TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40) UNIQUE
   STRING_  (FIRSTNAME, 40)
END_TABLE

It should work for most of the supported engines, including sqlite, I believe.

For two unique columns, there is DUAL_UNIQUE:
TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40)
   STRING_  (FIRSTNAME, 40) DUAL_UNIQUE(FIRSTNAME, LASTNAME)
END_TABLE


If you need even more columns to be unique, there is UNIQUE_LIST(u, l), where u stands for identifier and l is list of columns. I never used it but I think it should work like this:
TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40)
   STRING_  (FIRSTNAME, 40)
   STRING_  (CITY, 40) UNIQUE_LIST(address_key, FIRSTNAME, LASTNAME, CITY)
END_TABLE


If you have some troubles, it might be a good idea to check the schema of the created table, from command line (or other) client. Sometimes there are subtle bugs that prevent some parts of the schema to be created properly, and they're simply missing. Also any error messages and or logs might tell you if there is any hidden problem. Hope that helps Wink

Best regards,
Honza
Re: SQL : how to prevent duplicates ? [message #42393 is a reply to message #42378] Tue, 11 March 2014 11:04 Go to previous messageGo to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi, Honza,

Thanks a lot Smile

So, my way to do was good... The problem was that the index was already created as not unique by a precedent try, and I did not saw that... I dopped and re-created it as unique, and all works fine.

And now, I'll know how to do unique indexes on several columns Smile
Re: SQL : how to prevent duplicates ? [message #42399 is a reply to message #42377] Tue, 11 March 2014 15:08 Go to previous messageGo to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
I still have a problem : how can I display a message box when someone is trying to create or modify a record that has a duplicated data on a unique index ?

I tried what is explained in this doc, but sql.IsError() does not (no more ?) exists...

I tried with sql.GetErrorCode(), but the error code is always 0 ! However, the error is written to the log.
Re: SQL : how to prevent duplicates ? [message #42403 is a reply to message #42399] Tue, 11 March 2014 20:16 Go to previous messageGo to next message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

I think that IsError() become WasError() at some point in the history Smile

There is whole lot of well-documented error-related methods in SqlSession class:
bool WasError() const;
void SetError(String error, String stmt, int code = 0, const char * scode = NULL, Sql::ERRORCLASS clss = Sql::ERROR_UNSPECIFIED);
String GetLastError() const;
String GetErrorStatement() const;
int GetErrorCode() const;
String GetErrorCodeString() const;
Sql::ERRORCLASS GetErrorClass() const;
void ClearError();
void InstallErrorHandler(bool (*handler)(String error, String stmt, int code, const char *scode, Sql::ERRORCLASS clss));

They should solve any problem you have, at least in theory Wink

Honza
Re: SQL : how to prevent duplicates ? [message #42404 is a reply to message #42403] Wed, 12 March 2014 00:33 Go to previous message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
WasError() and GetLastError() do the job. But I don't understand why GetErrorCode did not.

My error was to look in Sql class instead of SqlSession class !

Thanks, Honza Smile
Previous Topic: Problem when growing a vector
Next Topic: Testscatter2 issue
Goto Forum:
  


Current Time: Thu Mar 28 20:45:52 CET 2024

Total time taken to generate the page: 0.01286 seconds