I have a junction table that has a PK made up of “articleID” and “memberID”, and I want to keep these
If I have a PK called (“id”) that uses the auto-increment data-type, when I do an INSERT, can I leave out “id” in the fields listing as well as leaving the corresponding VALUE blank?
Debbie
what you should do is remove this “id” field altogether and just use the two original columns as the PK
then the whole auto_increment problem goes away 
Okay, thanks!
Debbie
The link I posted shows you how otherwise post your sql code and table structure.
If I leave out the field it didn’t seem to work, but I know there is a way to do it?!
Debbie
did the insert work when you tried it? That would answer your query.
I assume you have a development environment in which you are building your website.
That would be my argument as well. What a convoluted scheme! shudder It’s one of those things where if you see you need it that way (or rather, think you need it that way) you really need to go back to drawing board and rethink stuff because you’ll regret it later if you don’t.
I never suggested anything, that is what the op is asking how to do. Is it a good idea, probably not none the less hat was the question I believe.
there is an exception, and that is if the relationship table has one or more child tables
so the relationship table has a unique key of (x,y) but a surrogate auto_increment PK, and the child table, which has multiple rows for each combination of (x,y), would then use a single column FK to link to the parent
i personally don’t like this but i’ve seen it done
it would be tough to come up with a realistic example of where the single column FK would be better, too, because the child table can easily have a PK of (x,y,z) and use (x,y) as the FK
my counter-argument for the surrogate in this case is: how do you navigate to a particular child row? it surely isn’t by the surrogate key value (because you don’t have it ahead of time), but rather by the (x,y) values, and then the z value, so you might as well store those in the child
I humbly disagree, you can still put a UNIQUE INDEX on the other two fields - easy as pie and no need to program anything.
Mind you I’m not saying that adding an auto_increment to a relational table is a good idea. It’s not. Never has been, never will be. No exceptions.
no she wouldn’t 
oddz, are you really suggesting a surrogate auto_increment key on a relationship table???
There can only be a single primary key. In this case you would want to replace the current primary key with a unique key instead and place the primary key on the id column.
MySQL (or phpMyAdmin) agreed with you last night! 
I DO have articleID and memberID as the PK.
I just wanted an “id” auto-increment field so I had a unique number for each record to help me easily identify records by number. (Its easier to find the latest record by looking at “27” than other fields including DateTime.
Oh well?!
Debbie
There can only be one key that is the primary key. It doesn’t have to be a single field in the key though. A key is allowed to contain multiple fields and you can have as many fields as you like in the primary key up to however many fields there are in the table. (that doesn’t mean that just because you can make all of the fields part of the primary key that you should but where you have a relationship table with two fields then both will be a part of the primary key).
You should never add an autoincrement field just for the sake of having a numeric primary key. Where a suitable field or fields that will uniquely identify each record already exist in the table and where those fields are relatively small in size then they are the obvious primary key.
Adding an autoincrement field to a table makes it a lot more complicated to check for duplicates - in fact for most circumstances where you don’t use an autoincrement the table will automatically ensure that there are no duplicates without you needing to write code to check.
i didn’t actually say that, because in most cases they are, but it’s not guaranteed
however, this still does not dictare the order that the rows are returned in
for instance, some of the rows could be sitting in the mysql buffer, from a previous query, so the database might be smart enough to return those first, and skip over them when retrieving the others
the only thing you ~can~ be certain of is that the result sequence is not guaranteed to be in any sequence whatsoever unless you use an ORDER BY clause
So you are saying that records are NOT physically stored in the table in the sequential order that they are inserted?
Record1
Record2
:
Record25
Debbie
But I was hoping to have things sorted by default by the date entered.
I know that having an “id” as a PK would do this, and I was hoping that adding an “id” that was unique would do the same and save me the “double-click”…
Debbie
If you’re using phpMyAdmin, just click on the DateTime field’s header twice, which will sort your entries by date time Desc, meaning your latest entry is at the top.
unfortunately this is a myth
the rows in a database table have no intrinsic order whatsoever
if you want to see them in a specific order, you must use an ORDER BY clause in your SELECT statement
even when you browse a table in phpmyadmin, phpmyadmin uses a SELECT statement to retrieve the rows
without an ORDER BY clause, you are getting the rows in any order that’s convenient for the mysql engine
when your tables get large enough, you will see what i mean
Interesting. I didn’t know that?!
Debbie