Auto Increment on Composite Primary Key

Defining Composite Key with Auto Increment in MySQL

You can't have MySQL do this for you automatically for InnoDB tables - you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.

Something like the following should work

DELIMITER $$

CREATE TRIGGER xxx BEFORE INSERT ON issue_log
FOR EACH ROW BEGIN
    SET NEW.sr_no = (
       SELECT IFNULL(MAX(sr_no), 0) + 1
       FROM issue_log
       WHERE app_id  = NEW.app_id
         AND test_id = NEW.test_id
    );
END $$

DELIMITER ;

auto increment on composite primary key

I am agree with Mr. Linoff's answer but if you want to store it phisicaly, you can do it within an insert trigger:

Update Your_Table
set SurfaceID =  ( select max(isnull(SurfaceID,0))+1 as max 
                  from Workspaces t
                  where t.AreaID = INSERTED.AreaID )

---

EDIT:*(as an example wanted for how to implement it)

In the question I have seen two table that's why I have wrote the code as above, but following is a sample for what I meant:

Sample table:

CREATE TABLE testTbl 
(
    AreaID INT,
    SurfaceID INT, --we want this to be auto increment per specific AreaID 
    Dsc VARCHAR(60)NOT NULL
)

Trigger:

CREATE TRIGGER TRG
ON testTbl
INSTEAD OF INSERT

AS

DECLARE @sid INT
DECLARE @iid INT
DECLARE @dsc VARCHAR(60)

SELECT @iid=AreaID FROM INSERTED
SELECT @dsc=DSC FROM INSERTED

--check if inserted AreaID exists in table -for setting SurfaceID
IF NOT EXISTS (SELECT * FROM testTbl WHERE AreaID=@iid)
SET @sid=1
ELSE
SET @sid=(  SELECT MAX(T.SurfaceID)+1 
            FROM testTbl T
            WHERE T.AreaID=@Iid
          )

INSERT INTO testTbl (AreaID,SurfaceID,Dsc)
            VALUES  (@iid,@sid,@dsc)

Insert:

INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V1');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V2');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V3');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V4');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V5');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V6');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V7');
INSERT INTO testTbl(AreaID,Dsc) VALUES (3,'V8');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V9');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V10');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V11');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V12');

Check the values:

SELECT * FROM testTbl

Output:

AreaID  SurfaceID   Dsc
   1       1        V1
   1       2        V2
   1       3        V3
   2       1        V4
   2       2        V5
   2       3        V6
   2       4        V7
   3       1        V8
   4       1        V9
   4       2        V10
   4       3        V11
   4       4        V12

IMPORTANT NOTICE: this trigger does not handle multi row insertion once and it is needed to insert single record once like the example. for handling multi record insertion it needs to change the body of and use row_number

Mysql: autoincrement field in composite primary key for each unique combination of other fields

It's only possible for (book,author,chapter) to be the primary key and for chapter to auto-increment independently for each unique (book,author) combination only in one circumstance:

The storage engine has to be MyISAM.

A MyISAM table defined like this will do exactly what you expect, starting column `c` at "1" for each unique (a,b):

CREATE TABLE `aa_test` (
  `a` varchar(48) NOT NULL,
  `b` varchar(48) NOT NULL,
  `c` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`,`b`,`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

However, don't do it.

For at least these reasons:

First, you shouldn't be using MyISAM. It is a very old technological dinosaur, it is not transactional, and it is not nearly as robust as InnoDB when it comes to corruption and crash recovery. You should be using InnoDB and this legacy behavior in MyISAM is no good reason to do that differently.

Second, that's not really an appropriate use of auto-increments. Auto-increments should really only be used for values that are needed as surrogate values but that have no actual meaning in the real world.

Third, your table does not appear to be well-designed, perhaps not even in 2nd normal form.

One of two things must be true based on this structure:

Either each chapter of a book can have a different author, which breaks the whole point of trying to auto-increment on (book,author) ... or (more likely) the presence of the author attribute on every row for (book,chapter) is redundant.

Arguably, the only candidate key in this table is (book,chapter) and if author does not depend on both book and chapter then the table isn't in 2NF.

Additionally, you shouldn't be storing the name of the book in each row, either. Any given piece of data should only appear in a single column in a single row in a single table in a relational database, with every thing else referencing it by a key (e.g., a "book_id" here references a single "id" in the "book" table which contains a column for the "title" of the book).

My answer, then, is a suggestion that you are not trying to solve the correct problem, and should consider redesigning your structure so that author is an attribute of book in a table where each book only has one row, and then you'd have this table with (book,chapter), and your application could populate those chapter numbers manually.

Alternately, if you insist on auto-generating these values, you could use a BEFORE INSERT trigger to calculate the appropriate value on insert, containing logic like this:

IF IFNULL(NEW.Chapter,0) = 0 THEN  
  SET NEW.Chapter = (SELECT COUNT(1) FROM this_table_name x
                      WHERE x.Book_Name = NEW.Book_Name
                        AND x.Author_Name = NEW.Author_Name) + 1;
END IF;

A trigger with this logic would work with any storage engine.

The IFNULL() is required because MySQL historically coerces a null to 0 before firing the trigger when the column is not nullable, even though this behavior is not correct.

Auto Increment Composite Primary Key

You shouldn't have the ProjectRef column at all. This violates basic rules of database normalization. If you want your front end to display the ProjectRef then just calculate it from the columns that you have.

How to set an autoincrement composite primary key in oracle19c?

Create trigger on this table to achieve this result. Assume table name is test123

create or replace trigger trg_test123 before insert on test123
for each row
declare
v_id1 number;
v_max_id2 number;
begin
v_id1 := :new.id1;
select nvl(max(id2), 0) into v_max_id2 from test123 where id1 = v_id1;
v_max_id2 := v_max_id2 +1;
:new.id2 := v_max_id2;
end;

Composite Primary Keys and auto increment? What is a good practices?

You definitely want to use autoincrementing id values as primary keys. There happen to be many reasons for this. Here are some.

1. Avoiding race conditions (accidental id duplication) requires great care if you generate them yourself. Spend that mental energy -- development, QA, operations -- on making your SaaS excellent instead of reinventing the flat tire on primary keys.
2. You can still put an index on (client_id, id) even if it isn't the PK.
3. Your JOIN operations will be easier to write, test, and maintain.

4. This query pattern is great for getting the latest row for each client from a table. It performs very well. It's harder to do this kind of thing if you generate your own pks.

       SELECT t.*
         FROM table t
         JOIN (SELECT MAX(id) id 
                 FROM table 
                GROUP BY client_id
             ) m ON t.id = m.id
   

---

Related Topics