Menu
Right now, I have a table whose primary key is an auto_increment field. However, I need to set the primary key as username, date (to ensure that there cannot be a duplicate username with a date).
I need the auto_increment field, however, in order to make changes to row information (adding and deleting).
What is normally done with this situation?
Thanks!
571
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.
AutoIncrement fields are integer in mysql. You can mirror the auto-increment field in a varchar field and create a trigger which updates the varchar field on insert/update.
Just set a unique index on composite of (username, date).
ALTER TABLE `table` ADD UNIQUE INDEX `name` (`username`, `date`); Alternatively, you can try to
ALTER TABLE `table` DROP PRIMARY KEY, ADD PRIMARY KEY(`username`,`date`); and I think in the latter case you need those columns to be declared NOT NULL.
111
I know this is old question, here is how i solved the problem -
ALTER TABLE `student_info` ADD `sn` INT(3) UNIQUE NOT NULL AUTO_INCREMENT FIRST
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With