While using composite Primary keys rental_id and customer_ id in the rental table for video rental physical modelling in MySQL, I’m facing an “ERROR: Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key”. Please help to explain why the error happens and how can I model the tables to avoid such errors.
You either have more than one AUTO_INCREMENT field in your table or/and an AUTO_INCREMENT field that is not a key.
Usually you use an AUTO_INCREMENT field as the primary key. It’s uncommon (and not always possible) to have AI fields in a composite primary key.
Why do you want to use a composite primary key in the first place? If you have a column rental_id in a table named rental anyone would assume that this is the primary key that uniquely identifies a column in that table.
We could think of replacing rental_id by a composite primary key consisting of customer_id and movie_id. IMHO that wouldn’t make much sense. E.g. it would prevent a customer to rent his favorite movie a second or a third time. I’d choose rental_id as the primary key and make it AUTO_INCREMENT.