jagguy Posted October 31, 2006 Share Posted October 31, 2006 Hi,q)I know a primary key is a unique identifier within a table eg personal information table has fields name ,address etc.I have a auto-increment field as a primary key because the name field might not be unique in such a table. I have the name field as an index, because mysql won't allow an index on a primary key , why?I thought an index is something you search on like a primary key and I am confused?q)When I delete a auto-increment primary key field I have then have the problem of say a missing number in primary key. eg I have 3 rows and I delete row 2 with primary key of 2(auto field). I now display 2 rows and i have a row with PK of 1 and another of PK of 3. How can I reset it so I have no missing numbers in PK so I should still have PK with 1 and 2 with relevant data, and not 1 and 3? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 31, 2006 Share Posted October 31, 2006 1) Primary keys are just special cases of unique keys -- you can have a unique index of any field.2) Don't worry about these numbers... and don't try to reset them. It's not a problem. Quote Link to comment Share on other sites More sharing options...
jagguy Posted November 1, 2006 Author Share Posted November 1, 2006 q)How can I reorder the numbers of this field after a delete, because I want to display the data on a webpage with an ordered number. I know it isn't a problem but I want ordered numbers.q)how can i set a foreign key in phpadmin as there is no foreign key option in the structure to set? eg for a basic master/child relationship. It seems easier to just run a few inserts and bypass the cascading operations, especially with new data eg personal info and a product qtyq)what is the difference between the innodb and other types of db's Quote Link to comment Share on other sites More sharing options...
fenway Posted November 1, 2006 Share Posted November 1, 2006 1) If you want them in a particular order, sort them by something else... if you must use the "INSERT" order, then store the date/time the records were added. You can't just change these numbers arbitrarily for existing records!2) You'll need InnoDB tables to set foreign key constrants.. I'm not sure how PHPMyAdmin handles this.3) Read the sticky on this board; InnoDB is not to be taken lightly. Quote Link to comment Share on other sites More sharing options...
jagguy Posted November 2, 2006 Author Share Posted November 2, 2006 Do you need to set FK's in a table as good practice? If you have mysql on the web it appears you would use innodb. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 2, 2006 Share Posted November 2, 2006 You need to have them -- whether or not you enforce them is really an application-based decision. For many applications, it makes no difference at all if some records get "lost". Quote Link to comment Share on other sites More sharing options...
gluck Posted November 2, 2006 Share Posted November 2, 2006 Primary key by default creates an index so don't worry about indexing your table any more. You could still add indexes if you want. There would be no way of keeping track of the deleted field. You pretty much need to perform a select before insert to make sure if the value you are inserting is present or not. If you have auto increment forget that then. User order by or any sort in the query to group the orders.InnoDb and MyIsam both allow foreign keys but only InnoDB enforces FK concept.InnoDb is transaction safe - ACID complaint where as InnoDB isn't and is mainly used for faster access. Storage size is way smaller in myisam Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.