U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » Community » Newbie corner » SQL : how to prevent duplicates ?

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

SQL : how to prevent duplicates ? [message #42377]

Mon, 10 March 2014 20:15

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

dolik.rce
Messages: 1791
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

Report message to a moderator

Re: SQL : how to prevent duplicates ? [message #42393 is a reply to message #42378]

Tue, 11 March 2014 11:04

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

Report message to a moderator

Re: SQL : how to prevent duplicates ? [message #42399 is a reply to message #42377]

Tue, 11 March 2014 15:08

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.

Report message to a moderator

Re: SQL : how to prevent duplicates ? [message #42403 is a reply to message #42399]

Tue, 11 March 2014 20:16

dolik.rce
Messages: 1791
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

Report message to a moderator

Re: SQL : how to prevent duplicates ? [message #42404 is a reply to message #42403]

Wed, 12 March 2014 00:33

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

Report message to a moderator

Previous Topic:	Problem when growing a vector
Next Topic:	Testscatter2 issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sun Apr 27 21:15:12 CEST 2025

Total time taken to generate the page: 0.02674 seconds