Tdolphin and Primary Keys

[vilian]

Hi Guys,

When i uploaded my dbf files to MySql using Dbf2Sql from Kleyber, a new field(ID) was added in all tables like a PRIMARY KEY. Is this really necessary? For Why?

[dutch]

Dear Vilian,

I learn the TDolphin also, ID is created as RECNO and the good point is Unique and never change even some record was deleted.

In my idea.
Dutch

[vilian]

Dutch,

But this field has hindered the treatment to prevent duplicate registration, Because it is the table primary key.

[Adolfo]

In Mysql you need to have an unique primary key (index), so as the routine (dbf2sql) creates the Mysql table from a DBF, it needs to create this new field to assign this primary key.

Keys, indexes on Mysql are quite important, not everyone understand its power. A good index model for your tables and DB, may help you a lot in improving query speed and maintenance.

Create indexes not only for ordering purposes, but thinking in the possible relations and queries you are going to have in your app.
You may have all the indexes you want, (simple and complex ones), the only problem would be the ammount of disk space used.
MariaDb, has a better performance compared to Mysql when refering to the use of indexes. In fact, the same table in Mysql and In MariaDb, with a single query using the index key is faster in MariaDb.

Hope it helps.

From Chile
Adolfo

[vilian]

Thanks Adolfo,

I know the index are important and a good index can improving query speed and maintenance. Because this, my question about ID Field.
Is It really necessary ?

[Adolfo]

The problem is that DBF2MYSL routine creates it.

Once created a primary key you can/have to change it to a "single unique key" with one of the fields you have in the table. Now if you have 50 tables, you'll have to do it 50 times.

Now. I see no problem to have a numeric field as autoincrement index key, there'll be no reason to slow down the query process, nor the performance of the DB in general, Maybe the disk space required to hold it, but nowadays, disk space is no problem at all.

If you create the Table from scratch, use the key you want, if the table is the result of a migration process, dont hesitate, let the Id be your primary key and create other indexes as you need or your DB model requires to.

From Chile
Adolfo