General Database Concepts

[fguy64]

greetings, I am just getting my feet wet with php and mySQL, and I wanted to discuss some basic concepts typical to all relational database systems.

We can start off with Primary Key fields and Index fields. If people have other RDBMS concepts to discuss, then I guess this would be a good place.

As I see it, loosely defined, an index field is a specially defined field in a table that aids in query and sort operations

for example, if you had a table called customers with fields cust_id, firstname, lastname, address, then last name might be an appropriate index field if you were doing queries such as "show me all the Smiths", or "sort this table by last name".

if you wanted to link to another table, say orders, then the last name would not be appropriate. intuitively, you can imagine the trouble you would run into with a large number of customers and you queried something like (show me all the orders from Smith). Cause Smith is not unique.

So you would query on cust_id, cause it is unique, and thus is called a "primary key field" for this table.

So based on this, I have a few questions to pose...

Is it correct to say that a primary key field is just an index field whose values are unique. ? Is this a useful distinction to make?

If this is true, then why is it when you are creating a table using the myPHPAdmin tool in cPanel, that part of the field definition includes radio buttons for Primary Key, Index, and unique. If my definition of index and primary key is true, then why do we need the "unique" radio button.

see picture below for reference.

[worldwise001]

These links may help you understanding this:

http://en.wikipedia.org/wiki/Primary_key
http://en.wikipedia.org/wiki/Index_(database)

The quick concise version is that the unique flag will force the data in those cells to be unique; primary keys are a subset of unique keys, in that they are forced to be non-NULL (i.e. they MUST have data in them).

Indexes exist as an optimized cell; if you search/sort by index, the time it takes to search/sort is noticeably faster than if you were to search/sort by other cells.

That's my understanding of it at least.

[fguy64]

Thanks bud. I actually ended up making a similar post in Programming Help forum, and there is lots of good discussion there. Probably a mod can delete this thread.