Oracle PL/SQL Multiple insert without duplicate value

Question

I have table call reviewbook table. Users need to assign 3 different review members to a bookid. They can be any reviewmemberid as long as they are 3 different ones to one bookid. Below is the result that I want. Right now, what i can think of is doing a multiple insert in a query however, the reviewmemberid can be duplicated, which is not what I want. I heard that trigger can be a way of doing this but I have no idea how to apply that. Can someone be kind enough to guide me or maybe if there is a better way of doing this.

   Reviewbook
   -reviewid
   -reviewmemberid
   -bookid

  reviewid  reviewmemberid  bookid
      1           2           1     
      2           3           1
      3           5           1
      4           1           2 
      5           2           2
      6           5           2
      7           1           3
      8           2           3
      9           4           3    



//My current insert code, but this insert code can insert duplicate reviewmemberid.
INSERT ALL INTO REVIEW (REVIEWID,REVIEWMEMBERID,BOOKID)VALUES
(?.?,?) INTO REVIEW
(REVIEWID,REVIEWMEMBERID,BOOKID)VALUES 
(?.?,?) INTO REVIEW
(REVIEWID,REVIEWMEMBERID,BOOKID)VALUES 
(?.?,?) SELECT * FROM DUAL;

Do you simply want to make sure that each combination of `(REVIEWMEMBERID,BOOKID)` appears only once, or is it more complicated than that? — Darwin von Corax, Apr 04 '16 at 03:59
Yes so 3 different reviewmemberids to one book id and that bookid is done. Users will proceed on assigning for the next bookid. — Jason Ching Yuk, Apr 04 '16 at 04:04
Would it be enough to put a unique index on `(REVIEWMEMBERID,BOOKID)`? — Darwin von Corax, Apr 04 '16 at 04:05
There is no PL/SQL in your question. Why do you think you need a stored procedure? — , Apr 04 '16 at 06:03

score 0 · Accepted Answer · answered Apr 04 '16 at 04:18

0

Another option would be to simply declare a unique index on the columns (REVIEWMEMBERID,BOOKID). The syntax would be

CREATE UNIQUE INDEX book_reviewer_idx
  ON review (reviewmemberid, bookid);

This will ensure that a particular pairing of (reviewmemberid, bookid) appears only once in the table; an attempt to insert a duplicate will raise an error.

answered Apr 04 '16 at 04:18

Darwin von Corax

5,201
3
17
28

1

Adding the constraint doesn't answer how to add without duplicates on the example in question. – George Dimitriadis Dec 04 '19 at 08:54
Sure it does. The constraint prevents any combination of `reviewmemberid, bookid` will appear only once, which is what the OP wanted. – Darwin von Corax Mar 07 '20 at 13:34

Oracle PL/SQL Multiple insert without duplicate value

1 Answers1