Question: I'm quite new to SQL and databases.

I'm trying to make a preference table of an user.
The fields will be `user_id`, `pref_no`, `prg_code`.

Now if I create the table making `pref_no` `auto_increment` then it will automatically increase irrespective of the `user_id`.

So, my question is - Is there a way to define the table in such a way that it will be `auto_incremented` taking `user_id` into account or I have to explicitly find the last `pref_no` that has occurred for an user and then increment it by 1 before insertion? 

Any help is appreciated. Thanks in advance.

Answer: 
Following what `Mjh` and `Fahmina` suggested, we can create a procedure for the insertion.

```
DELIMITER //
CREATE PROCEDURE test(IN u_id INT(7), p_code INT(5))
    BEGIN
        SELECT @pno:= MAX(pref_no) FROM temp_choice WHERE user_id = u_id; 
        IF @pno IS NULL THEN
            SET @pno = 1;
        ELSE
            SET @pno = @pno + 1;
        END IF;
        INSERT INTO temp_choice VALUES (u_id, @pno, p_code);
     END //
DELIMITER ;

```

Now we can easily insert data by using

```
CALL test(1234567, 10101); 

```
To manage user's preference, you don't need `user_id` to be auto\_incremented in this table, but `pref_no` has to be.

`user_id` will just be a refence (or foreign key in sql) to your user table (where `user_id` should be auto\_incremented).

And to request preference for a given user your request would be :

`SELECT * FROM [user table] INNER JOIN [pref table] ON ([user table].user_id = [pref table].user_id) WHERE [user table].user_id = ?`

(replace '?' by the user\_id you want)