INSERT with auto increment field

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 :wink:

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 :slight_smile:

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! :stuck_out_tongue:

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

:slight_smile:

Interesting. I didn’t know that?!

Debbie