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.
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.