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?
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.
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.
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.
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.
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
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”…
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.