Wednesday 25 January 2012

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.

3 comments:

Matt said...

Hi Scott

I was wondering whether you considered creating "instead of" triggers on the view.

This way you could keep your automatic row processing process and APEX would still do all the concurrent update checking. You can then control how the data is saved in the trigger, i.e. ignoring your rowid column and any others that might be read only etc.

I haven't tested this so I'm not sure, but it was just a thought after reading your post.

P.S. Sorry to hear about your mate.

Matt

Scott Wesley said...

Hi Matt,

I did consider "instead of" triggers at one point, but most have forgotten about it by the time I wrote up the post. I might investigate that further later,

Cheers

Christian Rokitta ♠ said...

Matt, Scott,

Instead of triggers offered a very flexible solution for tabular forms in APEX until 3.2. But from 4.1 you need to have a have a key-preserved table joined in your view to make it work. Have a look at my (slightly outdated) post on "Tabular Forms on Complex Views - using INSTEAD OF Triggers".

Cheers,
Christian