INDEX Naming Question

[SamMac]

I'm kind of new to databases, I took a couple classes a couple decades ago and I have been reading.
I'm building a database to catalog my music collection LP's, Tapes, CD's.
I built a Lamp server for the system m being Maria db.
I have a question about INDEX's, I know each table should have an index, should the names of the INDEX's be unique for each table?

[Nobbie]

No. I always call it "id" with no exception. Its the primary key. Makes it much easier to read and understand, the id is the id. Each table must have an id which holds the primary key. And dont forget the most important database rule:

The key is the key and nothing but the key.

Some (bad) programmers tend to take a family name or so as primary key, that is badly wrong. The key MUST NOT have any meaning, its only a uniq number in your rows of a table. And for naming a foreign key (thats a table column which holds the keys from another table) i always use the name "id_foreigntablename" (where foreigntablename is the name of the foreign table). For example, you have table with cars, each car is made by a carmaker (for example chevrolet), so i have two tables cars and carmakers.

Both tables of course have an id (the primary key) and the car table also has a column "id_carmakers, that holds the primary key of the carmaker of the car. If you understand that basic SQL rule, you already have reached a lot.

P.S.: A note on "INDEX". A primary key is a (uniq) special form of an INDEX. Theoretically, each column can be declared as INDEX. INDEXes as defined for faster search results. Searching over an INDEX is far quicker than over a not INDEXed field. But it comes at a high cost, when inserting a new record (row), all INDEX fields have to be rebuild be the database engine. That takes time. A table can have many INDEXes, but it only can have one primary key. So you actually are asking about the primary key, not about INDEX. Each table *should* have at least the primary key. The values of the primary key are unique for each table and you *should* define it as automatic increment (the database engine calculates the primary key for each new record). All the other INDEXes can be unique, but its not a must. For example, if you have table with family names, a family name can be an INDEX (if you plan to search for family names at runtime), but mostly it is not UNIQ (thats an attribut of an INDEX). Because for example MILLER is a very common family name and you have more than one MILLER in your table. If you define an INDEX as UNIQ, your database SQL engine will give you an error message, if you add a record with an already known value. So if you declare family name as UNIQ and already have a MILLER in your table, SQL will give you an error if you again enter a MILLER.