CODEWITHSUNDEEP
mysqlsql
lu5er
lu5er

Reputation: 3564

Create an auto_increment column based on a group in MySQL

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.

Upvotes: 0

Views: 69

Answers (2)

lu5er
lu5er

Reputation: 3564

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);

Upvotes: 1

mJehanno
mJehanno

Reputation: 866

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)

Upvotes: 0

Related Questions