fguy64
New Member
- Messages
- 218
- Reaction score
- 0
- Points
- 0
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.
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.
