Auto increment with phpMyAdmin.

[lkeeney]

I have a table set up with a filed called primary. It is not presently set up for auto-increment. Can I set it up for auto-increment now by using phpMyAdmin? and if so, how. I can't find any provisions in phpMyAdmin for doing this.

I am hoping I can do this without destroying the data already in the database.

Lawrence

[Mittineague]

You can ALTER tables using phpmyadmin. Log in, select the database, select the table, select the field, and then click change (the pencil icon).

Every table I have ever created has had an auto-increment id field, so I don't know what would happen if you made a field auto-increment after it had rows in it already. AFAIK it depends on the highest value in the field. but generally will start where the last left off. i.e. if the last id was 157, the next (the first auto-increment) would be 158 irregardless if rows 0 to 20 had been deleted (in other words it wouldn't start at 0 to fill in the lower numbers).

It might be a good idea to create a test clone of the table and experiment with that first.

[felgall]

From the structure tab you can set whether a field in autoincrement or not.

From the operations tab you can change the current value of the autoincrement.

[r937]

Every table I have ever created has had an auto-increment id field...

not picking on you or anything, because i know many people do this, but it is often unnecessary and sometimes even counter productive

do you routinely, as frequently as assigning an auto_increment, also declare a UNIQUE constraint?

if not, then "ur doing it wrong"

[felgall]

Very few of the tables I have ever created have ever needed an autoincrement field. Usually there is an obvious field or combination of fields that qualifies to be used for the primary key without needing to invent extra fields. It is often a generated value such as a product code but something that still has meaning beyond providing a unique value for the key - usually being used outside of the database as well as in.

[Mittineague]

Honestly no. In fact the only things I've ever UNIQUEd are fields like username, email, etc, where I didn't want duplicates. I guess I've always figured that auto-increment took care of that auto-magically. But it does make a lot of sense, I certainly wouldn't want duplicate id values either. Even if I use them almost exclusively for my benefit and don't use them for table-to-table relationships or in any "live" code (basically just to "tag" the rows). Although my database work is still advanced-newbie-ish at best, I've gotten somewhat better than my "monkey see, monkey do" days, but not much. I'm getting to understand how databases "work" some and I'm getting away from the simple "one table with everything in it" way of thinking, but I've still got a way to go before I attempt writing any complex architecture using relational tables, keys, joins etc.

You may be correct in saying I'm not alone in doing it wrong, but having company doesn't make it right. Thanks for pointing it out. I don't want to carry any bad practices along if I ever start writing databases where my inefficiencies will start to matter more and more.

[lkeeney]

Very few of the tables I have ever created have ever needed an autoincrement field. Usually there is an obvious field or combination of fields that qualifies to be used for the primary key without needing to invent extra fields. It is often a generated value such as a product code but something that still has meaning beyond providing a unique value for the key - usually being used outside of the database as well as in.

Are you saying I don't need an index field unless I use it external to the database? I have read where I should have one and that it should be auto incremented. If this is not needed for the proper operation of the database beyond my normal data I don't want to use it.

My data base is basically set up for reservations for a new company. They plan on setting up for four reservations a day. I am basing all my data on the reservation date field. When a potential customer wants to make a reservation I will check to see how many of the same date are listed in the reservation date field. If there are less than 4, I will allow them to make a reservation. Besides this date, the fields are first name, last name, address, city, state, zip, phone, email, and a few more specific to the reservation they want to make.

I want to keep the database as simple as possible.

So far, I have a field I called primary, but I could not find a way to make it auto increment. I have populated the database with test data. If I don't need this field I will just remove it.

I'm sure it is obvious by now, this is my first database application.

Lawrence

[felgall]

If there is already a field in the table that will always have a unique value and which doesn't need to be updated after the record is created then that can be used as the primary key. It is only when there is no unique valued field in the table or where you need that field to be easily updateable that you need to resort to creating a meaningless field to use as the primary key. Even then you only need it if it is going to be referenced from some other table.

[r937]

everything stephen has said so far is accurate, but in the particular case of people, it's ridiculously difficult to find a column or combination of columns that will reliably have unique values for people, so a surrogate key is almost always used

but here, it appears that the table is not about people, but about reservations

from the description of your situation, the primary key would be date + number (1-4)

tip: don't call it "primary" because that's a reserved word

p.s. stephen, a primary key can be updated quite easily, even if it is referenced by foreign keys (ON UPDATE CASCADE takes care of keeping them in sync)

[lkeeney]

Thank you Stephen and r937,

I am slowly beginning to understand.

I still have some questions in my mind about the key. Somewhere in phpMyAdmin, I read where it should be called "primary, and only primary". I tried this morning to find where I saw this, but I can't find it again.

Is it absolutely necessary for me to have a field with a unique value? If so why? Is it something MySql needs? I can't see where I, for my own use, need a unique value in any field.

If I need one, I can easily do as r937 mentioned about adding a number (1-4) to the date, but then do I need to do something else to indicate this is the primary key?

Where does one learn the finer details about this. Is there a on-line reference, or a book suitable for beginners?

One last question. If I ever need it, where under the structure tab do I find the way to auto-increment? I have spent hours trying to find this. The only reference I see to auto-increment is when you are going to move or copy a table.

Thanks again for you input on this.

Lawrence

[r937]

there will be a book about SQL suitable for beginners announced by sitepoint in early january

i would advise you not to use the "wysiwyg" panels of phpmyadmin until you have a complete understanding of how SQL works and how tables are designed

for the interim, do everything in the SQL window

for example,

CREATE TABLE reservations
( res_date DATE NOT NULL
, res_no TINYINT NOT NULL
, PRIMARY KEY ( res_date, res_no )
, lastname VARCHAR(50) NULL
, firstname VARCHAR(50) NOT NULL
, ...
);

and yes, at this point in your development, it is vital that you have a unique column (or set of columns) in every table

[lkeeney]

there will be a book about SQL suitable for beginners announced by sitepoint in early january

i would advise you not to use the "wysiwyg" panels of phpmyadmin until you have a complete understanding of how SQL works and how tables are designed

for the interim, do everything in the SQL window

for example,

CREATE TABLE reservations
( res_date DATE NOT NULL
, res_no TINYINT NOT NULL
, PRIMARY KEY ( res_date, res_no )
, lastname VARCHAR(50) NULL
, firstname VARCHAR(50) NOT NULL
, ...
);

and yes, at this point in your development, it is vital that you have a unique column (or set of columns) in every table

Thank you. I will check out the book, and follow your advice.

Lawrence

[felgall]

p.s. stephen, a primary key can be updated quite easily, even if it is referenced by foreign keys (ON UPDATE CASCADE takes care of keeping them in sync)

I was thinking more in terms of its use outside of the database and that changing it in the database would mean that any other references to it would be invalidated (such as on the printed reservation information in this particular situation where it would be the obvious value to print on all the paperwork that is produced so as to make lookups of the database when the paperwork is available much easier).