Mysql – auto_increment a field in MySQL that is not primary key

auto-incrementmysqlprimary-key

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!

Best Solution

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.

Related Solutions

Postgresql – How to reset postgres’ primary key sequence when it falls out of sync

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Source - Ruby Forum

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Solution

Related Solutions

Postgresql – How to reset postgres’ primary key sequence when it falls out of sync

Mysql – concatenate multiple MySQL rows into one field

Related Question