Compound primary keys in Application Express

As many Apex developers are aware, there is a limitation on the number of columns in a compound primary key when creating forms out of the box.

This was alleviated in 4.1 by the ability to use ROWID to identify the record.

I was asked the question recently on how to get around it back in Apex 3.x
I gave some suggestions, but didn't have the opportunity to investigate at the time. Of course, I was really curious so I gave it a go myself when I had a chance - so here goes.

First I defined and populated a simple table with a primary key containing three columns.

CREATE TABLE three_col_pk
  (my_year   number
  ,my_month  number
  ,my_loc    varchar2(1)
  ,my_data   varchar2(20)
  ,CONSTRAINT my3_col_pk PRIMARY KEY (my_year, my_month, my_loc)
)
/

INSERT INTO three_col_pk values (2011, 12, 'A', 'Scott');
INSERT INTO three_col_pk values (2011, 12, 'B', 'Kylie');
INSERT INTO three_col_pk values (2011, 12, 'C', 'Penny');
INSERT INTO three_col_pk values (2011, 11, 'A', 'Branka');
INSERT INTO three_col_pk values (2010, 12, 'A', 'Chris');
INSERT INTO three_col_pk values (2011, 11, 'B', 'Eddie');
INSERT INTO three_col_pk values (2010, 12, 'C', 'Ray');
INSERT INTO three_col_pk values (2010, 12, 'B', 'Kate');
INSERT INTO three_col_pk values (2011, 10, 'C', 'Lynda');

If you try create a standard Report/Form combination with this table using the wizards you will receive this when attempting to fetch a record:
ORA-01422: exact fetch returns more than requested number of rows

And if you attempt to create a new record, you'll receive this complaint depending on the column that is excluded from the screen:
ORA-01400: cannot insert NULL into ("SAGE"."THREE_COL_PK"."MY_YEAR")

So to get around this problem, the first thing that came to mind was to create a view that contained the rowid and build a Report/Form combination on that. I figured there'd be a few problems arising from this, but one must start.

CREATE OR REPLACE VIEW three_col_pk_vw AS
SELECT rowid my_pk
      ,z.*
FROM three_col_pk z
/

This certainly fixed my Automatic Row Fetch process, which now looked like this:

However, when I tested an update using the Apex form I received this error
ORA-20505: Error in DML: p_rowid=AAAGsHAAFAAAGRvAAA, p_alt_rowid=MY_PK, p_rowid2=, p_alt_rowid2=. ORA-01733: virtual column not allowed here

When I saw this I thought - does the automatic row update also update the primary key?
I have been curious about this in the past so I thought good a time as any to test it out.

Tangent - for players of the PL/SQL Challenge, this was a topic of a recent quiz.

To determine if Apex includes the primary key in the update within the Automatic Row Processing - I created an even simpler table with a primary key and a before update trigger that only fired when the primary key column was updated.
(Does anyone else have trouble consistently remembering create trigger syntax?!)

CREATE TABLE pk_test
  (a_pk number
  ,b date
  ,CONSTRAINT pk_test_pk PRIMARY KEY (a_pk)
)
/

INSERT INTO pk_test VALUES (1, sysdate);

CREATE OR REPLACE TRIGGER pk_test_trg
BEFORE UPDATE OF a_pk ON pk_test
FOR EACH ROW
BEGIN
  INSERT INTO pk_test VALUES (0, sysdate);
END;
/

I needed some way of identifying if the trigger fired. I've always found the idea of mutating tables funny, so I thought I'd just attempt to insert into the triggering table.

Sure enough, when I tested an update using the Apex form I received this:
ORA-20505: Error in DML: p_rowid=1, p_alt_rowid=A_PK, p_rowid2=, p_alt_rowid2=. ORA-04091: table SAGE.PK_TEST is mutating, trigger/function may not see it ORA-06512: at "SAGE.PK_TEST_TRG", line 2 ORA-04088: error during execution of trigger 'SAGE.PK_TEST_TRG'

So a way around that is to remove the automatic row processing created by the wizard and replace it with my own code. For simplicity, I just used the following:

BEGIN
  INSERT INTO three_col_pk
    (my_year
    ,my_month
    ,my_loc
    ,my_data)
  VALUES
    (:P9_MY_YEAR
    ,:P9_MY_MONTH
    ,:P9_MY_LOC
    ,:P9_MY_DATA);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  UPDATE three_col_pk
  SET my_data    = :P9_MY_DATA
  WHERE my_year  = :P9_MY_YEAR
  AND   my_month = :P9_MY_MONTH
  AND   my_loc   = :P9_MY_LOC;
END;

A merge would probably be more appropriate, but I have an issue with merge - for another blog post. And I couldn't be bothered working it out when this rolls off the fingers.

Hope you found that interesting.