About the UNIQUE Constraint… but on two Colums “together”

Databases
1

In a Data Base (SQLite), I have… First Name and Family Name… nothing fancy !

I want to set them a Constraint: UNIQUE. But I still want to be able to have Multiples First Names and Family Name like:

Joe Newbie
Jack Newbie
Joe Dupont
Jack Dupont

Many persons with the same Family Name… but with a different First Name
AND
Many persons with the same First Name… but with a different Family Name.

Is it possible ?

If the answer is “Yes”, please, how can I implement that ?

2

One way is to add a column which is the two names concatenated.
JackNewbie
JoeDupont

Or, in case there is any chance that two different names can combine to be the same one…

eg

Joe Tal
Jo Etal

Then you can concatenate with a character in the middle

Joe|Tal
Jo|Etal

However, from experience, Firstname and Surname are far from unique.
I personally know 3 Peter Smiths

You need a true UID - here in the UK that could be NI Number (although not everyone has one)
email address, or telephone can be problematic because people change those over time.

If this is a DB for personnel, use Employee ID / Payroll number
If it is for a club or user list, assign a membership number and use that.

3

Hi Jeff,

Thank you for your answer.

I asked this question for a data base who will store book writers (Edgar Allan Poe or Jules Verne). But the question arise some times ago about real life persons and I do not have a single idea then, so I dropped the idea (as too complex).

Joe Tal and Jo Etal
I do not think at that… (or forget this ever exist).

What I think but never implemented was to store the results (Hey, I poll a data base, so many results can be received) in a ListBox and let the use choose one…

I will let the things as they currently are. Maybe in the next weeks, I come with another idea (or enlarge the window to add a ListBox or use a Dialog Window with a ListBox and clear it when the user have choosed an entry…)

4

Create a unique index using both columns… no need to create a concatenated column.

-Karen

5

CREATE UNIQUE INDEX name ON table(column, column);

2 Likes
6

Question. You, by any chance, won’t have more than one “Jack Simpson”, for example, from a family in Alabama, and another one, not related “Jack Simpson”, from New York? (Homonym).

7

Who knows ?

I use this Data Base to store book writers names or it may be used to store something else, but the duplicate trouble may exists.

There was two Keith Richard(s) at a time (in the 60s)…

There was another Emile Schwarz in Alsace who dies some years ago (but was more famous than me)…

So, all is possible.

8

So , as I mentioned, you need to use something other than the name as a primary key, because it is easy to end up with two people using the same name.

Just as a quick example…

1 Like
9

OK, I get it now. I use a value for one Joe Newbie and set it to all its work and a second (and different / UNIQUE) value for Joe Newbie (#2) for all its work. So, beside a for Search feature (without its ID), where I will get two returned Records, there is clear “frontier” between them.

Seems clear now.