[solved] beginners question about primary key and unique
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-25 12:01

I have huge tables with decades of data. They are sorted by the unique
of "date and a 2 digit string".
No, they have no primary key.
I write them by searching date and string and I read them that way.

Many experts here told me, that lacking of primary key may cause
problems.
Well, they are right. This I found out by not having a primary key.
(although I am not sure, if THESE tables are the mess, I rather think,
deleted tables may cause it)

However there are 2 questions:
1) what about the size of the DB? hundred thousands of additions
primary keys take space
2) what about the existing data? They are MBs of figures organized in
a different way.

----------------------------------------------------------------------
Joanna · 2024-01-25 12:12

By primary key do you mean those numerical keys that are generated
automatically? I don’t use those either. I rely on column combinations
that are unique together. I didn’t like the idea of a special column
for primary key either because the database is large enough as it is.
I’m not a database expert though...

----------------------------------------------------------------------
rvk · 2024-01-25 12:34

That's just it. It's best to have a primary key as BIGINT and not be
dependent on columns which contain user data. Linking tables together
is also much better with foreign keys dependent on a single integer
key than on a combination of (maybe changing) fields.

Just google good database design and read a few papers about it.

@Nicole

1) A primary index of a BIGINT (via SEQUENCE/GENERATOR and TRIGGER)
will take less space than the constraint with the combo-fields you
have now. Think about it... your index now needs to store those in an
index so the size will more than a single variable. Using indexes and
foreign keys on a ID as BIGINT will be much more efficient.

2) If you want to do it correctly you would redesign the database and
pump over the data. But that will involve (re)designing a correct
database first.

BTW. The problems you are now having are due to deactivated
constraints and indexes. If they were in place, you could get away
with having the combi-constraints (although not as efficient, they
will do the job).

Edit:
A combo of user data as key is called "natural key".
https://www.sqlshack.com/sql-database-design-choosing-primary-key/
QuoteCons for using a natural key as a primary key
If a primary key is a combination of the several varchar columns it
becomes large. SQL Server will automatically add a clustered index on
a primary key, if a table already doesn’t have one. In this case an
index also becomes big (much bigger on varchar data type columns than
on an integer data type column) and the number of index pages which
are used to store the index keys is increased. This increases the
number of reads required to read the index and degrades overall index
performanceIf a primary key column(s) have varchar data type the JOIN
statements are slower as compared to the integer data-type joins

----------------------------------------------------------------------
Joanna · 2024-01-25 12:47

@rvk in my case the data in the very large table doesn’t change. So no
problem there. I also am not doing any joins to other tables. It’s
interesting though. I want to find out more about how databases work.

----------------------------------------------------------------------
alpine · 2024-01-25 13:27

@Nicole
Well, from a practical POV:

A table without a PK is called a "heap" table (HT)
A table with a PK is called a "index-organized" table (IOT)

The database file is structured in pages with a predefined size. All
low-level access to the file is on a whole page basis,

IOTs are kept in a B-tree structure, the whole data is kept together
with the PK value. B-tree structure is according to the order defined
by the PK.
HTs are kept in a linked list of pages, usually by the order records
were inserted. The only identifier of the record is called RowID -
combined number of page and page slot number.

B-tree structure for the IOTs gives an efficient means for searching
by PK and the benefit of having the rest of the data available without
the need to read additional pages. It is of big advantage when using
FKs and making JOINs.
HTs are overkill when accessing ordered data (ORDER BY, GROUP BY),
even with a secondary indexes available, huge number of pages must be
loaded just to satisfy the ordering.
HTs have some speed advantage over IOTs in INSERT but big
disadvantages in SELECT and UPDATE. Thus they can be used as a staging
areas e.g. for bulk operations.

Of course, IOTs with a poorly chosen PK can be also a big trouble -
GUID as a PK for example.

@rvk
Another disadvantage of the natural PKs is that they're "rigid" -
changing the value of a PK can bring a lot of headaches, e.g. with
data synchronization, confused data access components, etc.

----------------------------------------------------------------------
Zvoni · 2024-01-25 15:38

Quote from: alpine on January 25, 2024, 02:27:10 pmAnother
disadvantage of the natural PKs is that they're "rigid" - changing the
value of a PK can bring a lot of headaches, e.g. with data
synchronization, confused data access components, etc.
Have to disagree.
natural PKs are not rigid.
The Problem is a missing ON UPDATE CASCADE in the Foreign Key.

Example:
a Phonenumber is a "natural PK".
Why? Because +18005432235 exists only once in the whole world (the
number "prettified": +1-800-5432-235)
Same for your IBAN and other similiar Attributes

Now imagine an Address-Book-App
You have the usual "suspects" FirstName, LastName etc. blablabla...
and you have "PhoneNumber"
If you declare PhoneNumber as a PrimaryKey, and in a Childtable you
want a ForeignKey to the PhoneNumber (Say, tracking PhoneCalls), you
declare the ForeignKey with ON UPDATE CASCADE.

What happens?
For whatever Reasons, you change your PhoneNumber (new SIM-Card,
contract, whatever).
If you now change the Value of that natural Primary Key "PhoneNumber"
in your Master-Table, ON UPDATE CASCADE propagates the Change down to
each Foreign Key itself.

Yes, i'm aware that in case of a surrogate PK (which is the "official"
Name for those AutoIncrements Integers) you can solve that by
designing your queries differently.

It's more the Statement, that "natural PKs" are rigid, and can cause
headaches and what not, that bugs me the wrong way

That said: Everything said above is still correct and valid (Needed
Diskspace for combined "natural" keys etc.)

----------------------------------------------------------------------
Joanna · 2024-01-25 16:06

What if two different people share the same phone number though?

----------------------------------------------------------------------
alpine · 2024-01-25 16:32

@Zvoni
I'm sorry that I unintentionally allowed myself to make this statement
because now an epic discussion could follow about what exactly a PK
should be and I don't want to argue here. There are many arguments for
and against the natural PKs. Let's pretend I didn't say that.

BTW, I know very well about ON UPDATE CASCADE.

----------------------------------------------------------------------
rvk · 2024-01-25 16:35

Edit: Ha, already typed this... just see it as my thoughts 

Quote from: Joanna on January 25, 2024, 05:06:32 pmWhat if two
different people share the same phone number though?
Exactly. That's why phone number might have been a 'bad' example. What
about addresses without phone number?

Maybe BSN or "national identification number" would be better. A
unique number assigned by the government. But not everyone might have
such number. What about businesses. Or foreigners (where you might not
have such number).

The often used example of books... hey lets use ISBN. Every book has a
ISBN number which is unique? Doesn't it?
What about if you want to add scientific papers or something? You
can't because you don't have a ISBN number.

If you are absolutely sure the field will never change and MUST always
exist and not be NULL, then yes, you can use it as primary key.
But what about the efficiency. Fields with CHAR are not recommended to
be used as primary key. Woops...

Also... when using ON UPDATE CASCADE the set field needs to be updated
for those records. I don't like that idea at all.
Using a unique ID (generated) is a much more stable method.
I have LINKNUMMER (Dutch) for ALL my tables and foreign keys with
L_TABLENAME in the other tables.
All other fields can be changed without consequences and data can be
joined together.

It's been a while... but good database design also involves
normalization.
https://en.wikipedia.org/wiki/Database_normalization

Yeah, there is a lot about database design that you need to think
about.
For a straight forward small table you might get away with combining
character fields to a constraint or even primary key.
But you see what happens if the database grows to millions of records
and 14 years of data or the database becomes more complex

----------------------------------------------------------------------
Zvoni · 2024-01-25 17:20

Quote from: rvk on January 25, 2024, 05:35:38 pmEdit: Ha, already
typed this... just see it as my thoughts 

Quote from: Joanna on January 25, 2024, 05:06:32 pmWhat if two
different people share the same phone number though?
Exactly. That's why phone number might have been a 'bad' example. What
about addresses without phone number?

Ok.
@Joanna, look closely. There is a reason why i added area and country-
code to the phonenumber. That whole thing is unique in the whole
world. It‘s impossible for two people having the same phone number
contract wise with the provider, because how would the telekom network
know which phone to ring?

@rvk.
Ok agreed. There might be better examples. It doesn’t change the fact,
that in my example the phonenumber is unique, and therefore is a prime
candidate for a PK.
That said: don’t forget that you can link a foreign key to any column
of the master table with the corresponding datatype, and that column
doesn’t have to be a PK. It should be unique, though

----------------------------------------------------------------------
Nicole · 2024-01-25 18:26

WOW, how many useful insights!

I hope, this is not taken out of the context and sense-changing (from
Alpine):

Quotea poorly chosen PK can be also a big trouble

What is a good chosen and what a poorly chosen PK?

And does it make a difference, if the unique has a natural sort?
I'll give an example.

e.g. if the IBAN would serve as unique, nobody "knows" what is next.

In my table are COT values.
COT figures hold the business size of hedgers and fonds and small caps
in a certain week.

So the data may look alike this:
16.1.2024 10.000 pieces of the hedgers, 5000 pieces of small caps....
23.1.2024
....
See Screenshot of how this may look alike.
What you see at the screenshot is NOT requested as "order by datum",
but just as "select..."

This means:
Compared to the IBAN, the data are sorted in the input.
I am aware, that this needs not be necessarily like this. And
sometimes a value is missing, comes later and the order may differ
some days or some weeks.
But it never differs from the year 1990 to 2023.

In other words:
Does it make any difference, if the rows within the table are in a
kind of "grouped", because they were inserted each week, week after
week?
Or is it the same as the IBAN, where nobody know in which order it is
inserted?

What I mean:
The probability, that the the date of the 3rd week follows the 2nd
week is very, very high.
The probability, that the IBAN belonging to a certain bank and
contains its numeric identifier is low to unknown.

Is there a difference in the light of choosing a PK or not?

----------------------------------------------------------------------
rvk · 2024-01-25 18:58

Quote from: Nicole on January 25, 2024, 07:26:41 pmQuotea poorly
chosen PK can be also a big trouble

What is a good chosen and what a poorly chosen PK?At least you can't
go wrong with a unique generated ID

Using a date field combined with another column (a char column for
example) is not the way to go. It works but it's not efficient.

The sort is not a problem (gaps doesn't matter).
There it's always an index made for primary key and also for
constraints.
Ultimately indexes are fast and it doesn't really matter as long as
all values are unique. If the index tree becomes unbalanced (which can
happen) it's up to the database management to balance out the tree.

So your date and char column could better be served as constraint but
I would add a primary key ID to which you can link other records. (But
ultimately that's your choice.)

BTW... You did a SELECT. There should be a PLAN tab somewhere (or the
first tab?). There you should see the PLAN if your SELECT statement.
You want to have it show an index. If you see natural in there... then
it's going to be slow when using that in joins etc. If you don't have
indexes kor your indexes are disabled as was in your case) it will
show natural sort and that going to be slow when searching for records
with WHERE.

Edit: Firebird indexes might not even get unbalanced because of the
way they are formed.
See http://fb-list-archive.s3-website-eu-
west-1.amazonaws.com/firebird-support/2010/11/110653.html

----------------------------------------------------------------------
dseligo · 2024-01-25 19:38

Quote from: Zvoni on January 25, 2024, 06:20:51 pm@Joanna, look
closely. There is a reason why i added area and country-code to the
phonenumber. That whole thing is unique in the whole world. It‘s
impossible for two people having the same phone number contract wise
with the provider, because how would the telekom network know which
phone to ring?

You think it is unique but this is from a standpoint of a telecom
company.
But people can share a phone number (home phone number in a same
household, fax numbers in a office and similar).

I.e. wife goes to insurance company and they ask her for phone number.
She gives her home number. Couple of months later, husband goes to the
same insurance company and when they try to enter his data, database
refuses because phone number is already used.

For telecom companies phone number are OK so they know who to send
bills, but it is not enough. My mobile provider enables me to have
multiple SIM cards with same phone number (up to 3, and all phones
ring at the same time, until one is answered). So they have to store
multiple SIM card serial numbers combined with one phone number.

----------------------------------------------------------------------
Joanna · 2024-01-26 01:18

@Dseligo
It gets even worse....
How about when they decide that everyone has a cell phone that must be
used for two factor authentication before you can log into your
account?

----------------------------------------------------------------------
rvk · 2024-01-26 07:02

Quote from: Joanna on January 26, 2024, 02:18:09 am@Dseligo
It gets even worse....
How about when the decide that everyone has a cell phone that must be
used for two factor authentication before you can log into your
account? 
Actually that won't be a problem.
You can assume someone gives that number (and knows about the 2FA)
they have access to the phone. The 2FA on itself doesn't hinder same
phone number for different accounts.

But it's just not a good primary key because it's not unique.

----------------------------------------------------------------------
Zvoni · 2024-01-26 07:25

OK, let's get this straigt for TS, since she asked about "concepts"
for Primary Keys.
And i'm not denying that my example of Phonenumbers might have been
poorly chosen.

I'm leaving surrogate Primary Keys (Integer Auto-Increments) out of
this.

The decision which column to use as a "natural" Primary Key always
(!!) depends on the "environment" the database is to represent, and
its tables which represent "entities".

For example let's take a Company like Apple.
They produce devices (Phones, tablets, Computers, TV's, iPods etc.).

That leads to the first thought of "Hey, i'm going to need a table
'Category'"
There are entries like "SmartPhone", "TV", "Speech Controled",
"Tablets" and similiar
Hmmm..... On first look, there is no Attribute of those entries
presenting itself as UNIQUE (in the Environment!!) i could use as a
natural Primary Key, EXCEPT if i stipulate a RULE, that no Designation
is allowed to be entered twice.
OK, let's do that, since my Name is Steve Jobs, and i make the laws in
my Company.
Cat-Designation must be UNIQUE and is a Primary Key

The next thought might be "I need a 'Model'-Table"
There might be Entries like "iPad 2", "iPad 3", "iPhone 5", "iPhone
5S", "Alexa" etc.
Again the same deliberation as above for Categories, but additionally
i need a Foreign Key to the PK of Categories.
Let's use a String-Column pointing to "Cat-Designation", and let's
declare the Designation of the Model to be UNIQUE

The third thought is "Yes, i finally arrived at the table 'Products'"
It has Entries for each device like "RAM", "CPU", "Serial No" etc. and
a Foreign Key String-Column pointing to "Model-Designation"
.... "Hey... a Serial No is UNIQUE IN MY ENVIRONMENT because it's used
to identify a single product. Let's use it as a Primary Key"

I could go on and on....
The thing is: with an ON UPDATE CASCADE, let's say in Model-Table
there was a Typo "iPhoen S5", and someone goes on to correct it, that
correction will get propagated to all child tables using that Column
as PK for their FK.

Another Upside: Let's say your Boss gives you the task "Hey, how many
iPhone 5S did we produce last year?"
In your Classic Setup with surrogate anonymous PK's you would have to
JOIN two or more tables
In my Setup it would be a single Query
SELECT COUNT(SerialNO) As Cnt FROM Products WHERE
ForeignKeyToModel='iPhone 5S' AND YearOfProduction=2023

Yes, by all means, use surrogate PK's (which IS kind of "state of the
Art"), but don't forget, that "natural" Keys still have their right to
exist.
They depend on the Environment they are used in, and the RULES that
have been defined.
Yes, they cause "redundancy" of "natural" Data, but don't forget the
Concept of Primary/Foreign Keys in Databases is older than the modern
Database-Systems we use.

As a final comment, and this is real world:
The company i work for have a IBM DB2 on an iSeries as a central
Database.
It has some 200 schemas, with some 5000 (!!) tables, views and whatnot
in the main schema.
And you know what? Not a single surrogate Primary Key!

----------------------------------------------------------------------
alpine · 2024-01-26 09:05

Quote from: Zvoni on January 26, 2024, 08:25:21 amOK, let's get this
straigt for TS, since she asked about "concepts" for Primary Keys.
Referring back to the first TS post (although she started so many
threads in a succession that I'm not sure where am I currently) - Her
tables doesn't have a PK, which means they're effectively a heap
tables. She was advised (in other thread) to put a such in order to
internally convert the tables into "clustered" ones, i.e. properly
placed into a B-tree.

A good DB schema design (incl. choosing a suitable PK) is another
broad subject and it, perhaps, implies a complete overhaul of the TS
database.

As I suspected, the thread will turn into an argument about the pros
and cons of natural PKs, then into debates about normal forms, data
redundancy, etc. Database design is really a "state of the arts" and
so vast that would probably require its own sub forum. But I believe
there are many other forums specifically for this elsewhere.

Quote from: Zvoni on January 26, 2024, 08:25:21 amAs a final comment,
and this is real world:
The company i work for have a IBM DB2 on an iSeries as a central
Database.
It has some 200 schemas, with some 5000 (!!) tables, views and whatnot
in the main schema.
And you know what? Not a single surrogate Primary Key!
Because it was made "by the book". I respect that.

I also work mostly with such an "orthodox" DBs into the "real world",
and from my experience, I can allow myself to say that this is not
always the best solution (strongly depending on the specific case).
Non-natural PKs can be crafted in a way that will not sacrifice any of
the principles of the canonical DB design.
 
BTW, how many times in your practice an unsuitable item was chosen for
the PK? And what consequences did that have? Since often the bad
choices were discovered late into the life-cycle when the DB is
already online and with plenty of data in it.

----------------------------------------------------------------------
Nicole · 2024-01-26 10:08

@alpine: I am female.
I am thrilled by the discussion about primary keys. This is something
the experts here know and no book will teach me in this way.

There is one disadvantage of not having a primary key, which shall not
be unmentioned:
This is IBX.

Let me explain: I came from Delphi and Firedac. There I worked with
tables and had forms in my app which allowed to edit them.
Worked fine. Really fine. I could take a query, the results were
displayed and I could edit them as I were in Excel.

My migration to Lazarus brought the end of Firedac and my beginning
with IBX.
Do not misunderstand! I LOVE IBX and the support for it here is that
great, that Embarcadero can only dream of it.

However, this very point of working on with my database in an Excel
feeling, - ended in a disaster for me.
IBX took for granted that every table would have primary keys.
As some tables of mine had none - IBX deleted my data in huge and wide
ranges onEditing innocent fields.
We know, "no backup, no mercy". And I had a backup.

Never the less, IBX destroys databases if somebody uses it the look an
feel way.
Yes, sure, it was my mistake that I did not write the appropiate SQL-
statements.

However, I would prefer, that IBX starts to "blink red" if a no-
primary-key-table is combined with IBX in a way, where data loss may
occur.

And to sum-up:
I think, I will add pks on migrating and repairing my db.
Thank you for all the hints and links. Everything is saved in my ToDo-
File in the meanwhile.

----------------------------------------------------------------------
rvk · 2024-01-26 10:49

Quote from: Nicole on January 26, 2024, 11:08:59 amHowever, this very
point of working on with my database in an Excel feeling, - ended in a
disaster for me.
IBX took for granted that every table would have primary keys.No, IBX
can work on tables without primary key.
The problem you were having is that your constraints and foreign keys
were DEACTIVATED at some point.
After that, duplicate records could be added and the whole mechanisme
of preventing it in the database was inactive.

That's not IBXs fault. It would also have happened in FireDac or any
other client.

Adding or reactivating those constraints and indexes would have fixed
that.
A red flag would have been that b a backup/recover cycle would have
failed.

(backup/recover every few months should be part of your backup-
strategy just to check if the backup is working correctly !!!)

----------------------------------------------------------------------
alpine · 2024-01-26 11:32

Quote from: Nicole on January 26, 2024, 11:08:59 am@alpine: I am
female.
I sincerely apologize for my incorrect use of pronouns.

Quote from: Nicole on January 26, 2024, 11:08:59 amI am thrilled by
the discussion about primary keys. This is something the experts here
know and no book will teach me in this way.
Perhaps you should read a book on this, as this is how all expertise
begins.
Lets start with a definition: The primary key is the minimal set of
attributes which uniquely identifies a tuple (a.k.a. record) in a
relational table. Clear enough?

----------------------------------------------------------------------
Zvoni · 2024-01-26 11:44

Quote from: Nicole on January 26, 2024, 11:08:59 amI am thrilled by
the discussion about primary keys. This is something the experts here
know and no book will teach me in this way.
Not to be outdone by Alpine 
https://en.wikipedia.org/wiki/Library_catalog

I always love to refer to this when discussing "Databases"
Reason? A "Card Catalog" (Yes, Analogue on Paper/Cardbox!!!) IS a
Database (and no, we're not going to discuss "efficiency"!)

Or better said: The Library is the Database, and the "Cards" are
"premade" Queries

Nicole, read that article, and try to "classify" the information you
are given there.
Try to find out, what could be used as a Primary Key, and what not,
and the reasons for it

----------------------------------------------------------------------
Zvoni · 2024-01-26 12:01

Quote from: alpine on January 26, 2024, 10:05:51 amBTW, how many times
in your practice an unsuitable item was chosen for the PK? And what
consequences did that have? Since often the bad choices were
discovered late into the life-cycle when the DB is already online and
with plenty of data in it.
In the company i work for, none, since it is an industry-grade ERP-
System (which the costs for went into the 7-digits region)

In my case, since i have to write the "reports " (a.k.a SQL-Queries),
it's often times a PITA, to figure out, which Column in this Table
points to which column in another table, but that's due to poor
documentation, and not the design itself.

Don't get me wrong: In my own personal projects i use surrogate PK's
exclusively, but that's just because i'm a lazy bugger, and i don't
want to think about which columns can i combine to build a Primary
Key.

And remember: The "AUTO_INCREMENT INTEGER Primary Key" is not that
old, compared to how old the concept of Databases is.
What were the "usual" Databases some 30-40 years ago?
Yes, dBase and its sisters in the "personal"/"small business" sector,
and the DB-engines/Systems then didn't have an inbuilt
algorithm/mechanism to "GENERATE" those ID's, so it was normal to use
"natural" Primary Keys

Hell, i still remember in the mid-90's, when that technician from UPS
came to our company to install the then DOS-based UPS-Software.
Have a guess what the underlying DB-System was?....  Yes....
dBase...... and i had to connect a Dial-Up-Modem to that Computer for
the UPS-Software to call home....

Or a client of ours (also in the mid-90's) was really proud, that he
"programmed a Software to keep track of the consumption of your
Products...... in Clipper...." (anyone remembers Clipper?)

EDIT: Found it
https://en.wikipedia.org/wiki/Relational_model
There. The concept of relational Databases was first described in 1969
(Apollo 11 anyone?)

----------------------------------------------------------------------
Joanna · 2024-01-26 12:14

@rvk it has to be a phone number that allows texting. So ordinary
landline will not work. Some places have robots that can call
landlines others not. And of course there is no way to opt out of 2fa!

Even if someone is dealing with a phone company they could have two
numbers billed to same person so it’s not necessarily a one to one
relationship.

----------------------------------------------------------------------
alpine · 2024-01-26 16:28

Quote from: rvk on January 25, 2024, 05:35:38 pmMaybe BSN or "national
identification number" would be better. A unique number assigned by
the government. But not everyone might have such number. What about
businesses. Or foreigners (where you might not have such number).
Like the cases sometimes shown on the evening news: Sheltered refugees
who cannot benefit from their approved aids since the government ISs
are built around those personal identification numbers. Or a child
that can't receive a social care, because it's mother lives as a
hermit and she refused to register it.

Same with the VAT# and all the insanity of the constantly changing
legislation. (My focus is on PK candidates, not on conspiracy
theories)

Quote from: Zvoni on January 26, 2024, 01:01:14 pmAnd remember: The
"AUTO_INCREMENT INTEGER Primary Key" is not that old, compared to how
old the concept of Databases is.
However autoincs are not the only surrogates used. What about M$
extensive use of GUIDs in their RDBMS? Even more so now with the
proliferation of cloud services, UIDs are used as a remedy for
everything. Just awesome. 🤢

Quote from: Zvoni on January 26, 2024, 01:01:14 pmWhat were the
"usual" Databases some 30-40 years ago?
Yes, dBase and its sisters in the "personal"/"small business" sector,
and the DB-engines/Systems then didn't have an inbuilt
algorithm/mechanism to "GENERATE" those ID's, so it was normal to use
"natural" Primary Keys
dBase et al, they lacked notion of the relational model.

----------------------------------------------------------------------
rvk · 2024-01-26 17:05

Quote from: Zvoni on January 26, 2024, 01:01:14 pmWhat were the
"usual" Databases some 30-40 years ago?
Yes, dBase and its sisters in the "personal"/"small business" sector,
and the DB-engines/Systems then didn't have an inbuilt
algorithm/mechanism to "GENERATE" those ID's, so it was normal to use
"natural" Primary KeysI used BTRIEVE from 1988 onwards (then owned by
Novell and incorporated in Novell NetWare). (That's 36 years ago  )
BTRIEVE already had an AutoIncrement field. I used a LINKNUMMER for
all my tables since then (to link the tables together).

I also had the choice for Paradox (for DOS), later owned by Borland,
but it wasn't as established back then.
Microsoft SQL Server came just a bit later on. And there weren't many
other relational databases back then. Even Oracle wasn't there yet.
Only the big mainframe ones but that was overkill for normal desktop
PC (Compaq DeskPro 386 with 40MB harddisk).

But as far as I can tell, all had autoincrement options.
Even the IBM DB2 and Oracle supported autoincrement (IDENTITY) fields.

And indeed dBase didn't have it. Because it was basically flat-file(s)
database it didn't have the need for autoinc. (Later version did
implement it.) But I never considered dBase because I needed to have
multi-user database engine with locking mechanisme on record level.

Aaaah. Memory lane

----------------------------------------------------------------------
Joanna · 2024-01-26 22:29

@alpine “national identification numbers” are tempting to use as keys
because people have the misconception that they are permanent and
unique. Their over use has not only destroyed privacy but also made
them a prime target for hackers and identity thieves.

I have a question about the automatically generated primary key.
Wouldn’t it be unique to the table it was generated in so couldn’t be
used for joins to other tables? I’ve never used it so..

----------------------------------------------------------------------
rvk · 2024-01-26 22:57

Quote from: Joanna on January 26, 2024, 11:29:47 pmI have a question
about the automatically generated primary key. Wouldn’t it be unique
to the table it was generated in so couldn’t be used for joins to
other tables? I’ve never used it so..
Sure they can. They only need to be unique for that table.

I have linknummer for al my tables.
Land has a generator for it's linknummer
Branche has a generator for it's linknummer
Relatie had a generator fir it's linknummer
Etc.

Now relatie (client) as a field l_land
It contains the linknummer for the appropriate land of that client.
The linknummer for that client has nothing to do with that and can be
the same range.

Selecting relaties (clients):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT r.*,
l.naam FROM relatie rLEFT JOIN land l ON l.linknummer=r.l_land
This will give you all fields for relatie plus the name of the land of
that relatie.
So you see, the id (linknummer) only needs to be unique within that
table.
But every table had its own field linknummer (=id).

----------------------------------------------------------------------
dpremus · 2024-01-27 00:55

Quote from: Nicole on January 26, 2024, 11:08:59 am@alpine: I am
female.
I am thrilled by the discussion about primary keys. This is something
the experts here know and no book will teach me in this way.

Hi, we need more information to help you.

You mention Delphi and FireDAC. What database have you accessed with
Firedac?

If you worked fine without primary keys, maybe, for your needs, you
don't need a relational database at all. We need more information
about your data to give you the right answer.

If you are editing records without SQL, using the "edit", "append",
"delete", or "post" methods, and the database that you are accessing
supports only SQL, then your components will generate SQL commands
behind the scenes, so having a primary key will simplify the "where"
clause of SQL and speed up access.

You can simply add auto-increment primary key fields to your table.
For example, if you use the int32 type, this will add only 4 bytes to
each record, and you can cover 4,294,967,295 records.

But if you want to work with relational databases in the right way,
you need to structure your database to avoid redundancy. You need to
analyze your data to identify entities and the relationships between
these entities. After that, you are creating tables, fields, primary
and foreign keys, indexes, constraints, etc.

----------------------------------------------------------------------
dpremus · 2024-01-27 01:30

Quote from: Nicole on January 25, 2024, 01:01:03 pmI have huge tables
with decades of data. They are sorted by the unique of "date and a 2
digit string".
No, they have no primary key.

If found this from your another post

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBCOT(  DATUM DATE NOT NULL,  FK_COMM CHAR(2) NOT NULL, 
SPEC_LONG BIGINT,  SPEC_SHORT BIGINT,  COMM_LONG BIGINT,  COMM_SHORT
BIGINT,  SMALL_LONG BIGINT,  SMALL_SHORT BIGINT,  SPEC_NET INTEGER, 
COMM_NET INTEGER,  SMALL_NET INTEGER,  CONSTRAINT UNQ_TBCOT_COMM_U_DAT
UNIQUE (FK_COMM,DATUM)); 
Your table already have primary key, "DATUM" and "FK_COMM" are unique
and not null this is the same as primary key;

you can find more information here:
https://ib-aid.com/en/articles/database-constraints-in-firebird-and-
interbase/


If you want, you can change this unique to the primary key, but if
everything works, I would rather not change anything.

Alter table TBCOT drop constraint UNQ_TBCOT_COMM_U_DAT;
Alter table TBCOT add constraint PK_TBCOT_COMM_U_DAT primary
key(FK_COMM,DATUM);

You can ignore my previous post I didn't know that you have some
legacy FirebridSQL database.
Regards

Piko

----------------------------------------------------------------------
Joanna · 2024-01-27 13:21

@rvk that sounds kind of confusing ..
I was always under the impression that relational database tables had
columns with values that could be joined on.

An automatically generated number primary key makes no sense to me
besides being used as unique account number or something like that.

----------------------------------------------------------------------
Zvoni · 2024-01-27 14:09

Quote from: Joanna on January 27, 2024, 02:21:26 pm@rvk that sounds
kind of confusing ..
I was always under the impression that relational database tables had
columns with values that could be joined on.

An automatically generated number primary key makes no sense to me
besides being used as unique account number or something like that.
1) joining two tables on a „common“ column is the „relation“ in
„relational“. The columns don’t have to be Key-Columns
2) an auto ID as a primary key is mainly due to needed (disk) space.
An auto id is 4 or 8 bytes in size, while above discussed „natural“
PKs usually exceed those 4/8 bytes.

And to get any misconception out of the way: even with auto id as PK,
you still have to provide the value for it to the foreign key of any
childrecord you want to insert

----------------------------------------------------------------------
rvk · 2024-01-27 14:11

Quote from: Joanna on January 27, 2024, 02:21:26 pm@rvk that sounds
kind of confusing ..
I was always under the impression that relational database tables had
columns with values that could be joined on.
No no.
You can still join on different columns but it's not efficient.
Can you show me an example of what you mean because I think that you
would only do that when you have a database that is not normalized.

Non-normalized databases are not efficient in space and speed.
Non-normalized databases have columns duplicated (like the fields you
want to join on) while this is not needed and not desired on
normalized databases.

For example, a phonenumber, name or country-name should only just
exist in one table in your database. Not in multiple tables. And if
they are in one place, it doesn't make sense to use them in a join (as
key for joining).

----------------------------------------------------------------------
Zvoni · 2024-01-27 14:26

@rvk
That‘s because, columns declared as primary/foreign key in modern DBMS
get an autogenerated index for free. Nobody prevents me to create an
index for a UNIQUE String-Column which is not a Key and then the joins
on that column start being fun to work with

----------------------------------------------------------------------
Joanna · 2024-01-27 14:39

@rvk suppose you have a
table 1 
account numbers and names of person to bill etc
The primary key is account numbers
 table 2
phone numbers and calling activity with account numbers for billing .
Primary key is phone number

You still need an account number in both tables to link them together
right?

----------------------------------------------------------------------
rvk · 2024-01-27 15:13

Quote from: Zvoni on January 27, 2024, 03:26:52 pmThat‘s because,
columns declared as primary/foreign key in modern DBMS get an
autogenerated index for free. Nobody prevents me to create an index
for a UNIQUE String-Column which is not a Key and then the joins on
that column start being fun to work with
Yes, but you are still duplicating user-data fields (often character
fields) which take up more space and are less efficient.
I thought the usual advice was to have a ID and link the foreign keys
to that ID from another table.
Duplicating a character field (for example phonenumber or country name
or else) in another table and using it as foreign key just seems not
right to me (despite being possible).

BTW. Creating indexes on those character fields is something
different.
For example... you can create an index on phonenumber or countryname
or clientname and still do the join on ID. In that case the correct
index should still be chosen.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM RELATIE RLEFT JOIN PHONENUMBERS P ON P.REL_ID=R.IDWHERE
P.PHONENUMER='1234'
Using a character field as a foreign key in a database is generally
not advised. Foreign keys are typically used to establish
relationships between tables, and they reference the primary key of
another table. Primary keys are usually integer or numeric types for
efficiency and indexing purposes.

Using character fields as foreign keys can lead to performance issues,
increased storage requirements, and could complicate querying. It's
recommended to use numeric or integer fields as foreign keys for
better indexing and faster join operations.

And maybe this answer is also a good read:
Database development mistakes made by application developers

But I see in that answer that there is some contraversie about this 
QuoteThis is a somewhat controversial topic on which you won't get
universal agreement. While you may find some people, who think natural
keys are in some situations OK, you won't find any criticism of
surrogate keys other than being arguably unnecessary. That's quite a
small downside if you ask me. Remember, even countries can cease to
exist (for example, Yugoslavia).
Quote from: Joanna on January 27, 2024, 03:39:00 pm@rvk suppose you
have a
table 1 
account numbers and names of person to bill etc
The primary key is account numbers
 table 2
phone numbers and calling activity with account numbers for billing .
Primary key is phone number

You still need an account number in both tables to link them together
right?
If account number is a user-number (which can change and is user
assigned or character field) then no, I wouldn't want to duplicate
that to the table 2.
I would create an ID in table 1 and link table 2 trough foreign key
with that ID. (BTW table 2 should also get its own ID as in the
example below)

You say you would make phonenumber primary key in table 2. I would
also just make its own ID there. You might want to use this table
somewhere else (billing table itself or voicecall file) and you don't
want to use phonenumber in that case to link to it. That would become
a MESS.

Code: [Select]TABLE CLIENT:
ID
ACCOUNT
NAME
ADDRESS

TABLE CLIENT_PHONENUMBER
ID
ID_CLIENT
PHONENUMBER

TABLE CALL_HISTORY:
ID
ID_CLIENT
ID_CLIENT_PHONENUMBER
CLIP (actual phonenumber called)
DATE
TIME
SUBJECT
MEMO

TABLE CALL_VOICELOG:
ID
ID_CALL_HISTORY
WAVEFILE
You see a table CLIENT and CLIENT_PHONENUMBER.
If a client can have x number of phonenumbers you'll need to put those
in a different table.
(If you just have one phonenumber you can just put it in the main
table but another example would be multiple contactpersons per client)

Further there is CALL_HISTORY.

You can link that to CLIENT via ID_CLIENT. If ID_CLIENT_PHONENUMBER is
mandatory you could omit the ID_CLIENT because you could get the
client ID via the CLIENT_PHONENUMBER table.

Then there is CALL_VOICELOG for saving the sound wave of the call.

Linking all those via phonenumber would really be a mess if you expand
your database to include more functionality.

This is all about normalizing the database.
I agree, you shouldn't get overboard with normalization but you do
need to plan for expansion. And that begins with choosing the correct
primary key (and how you are going to link tables together). And in my
opinion this should be done through the primary key which (in
preference) should be generated and not user based and certainly not
character based.