U++ forum: Welcome to the forum

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