What Happens if I REST Enable a table with no Primary Key in ORDS?

thatjeffsmith ORDS 1 Comment

Tell Others About This Story:

When you AUTO-REST enable table in ORDS, we publish a full API for you:

the /{id} part is important.

We auto find your Primary Key Column and use that to address particular rows (items) in your table (collection.)

But..what happens if you don’t have a primary key?

CREATE TABLE no_keys (letter CHAR(1), letters VARCHAR2(25), numbers INTEGER);
 
INSERT INTO no_keys VALUES ('a', 'abc', 1);
INSERT INTO no_keys VALUES ('a', 'abc', 1);
INSERT INTO no_keys VALUES ('b', 'abc', 2);
INSERT INTO no_keys VALUES ('c', 'abc', 3);

REST enable that…

BEGIN
 
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'NO_KEYS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'no_keys',
                       p_auto_rest_auth => FALSE);
 
    COMMIT;
END;
/

And now let’s ‘GET’ the table.

Those look like…ROWIDs?

So let’s try that…

Wait, what?

Hmmm, that ‘%2’ part doesn’t look right to me.

That’s actually ‘%2B’ which is a way to escape a ‘+’ in a URL.

So if we replace that appropriately, we can confirm that’s indeed the ROWID.

Getting the record via ROWID.

By the way, ROWIDs can change. You should probably try to at least have some UNIQUEs on your table, if you can.

And if we go the OTHER way, More than One Column in my Primary Key?

CREATE TABLE THREE_KEYS 
(
  COL1 CHAR(1) NOT NULL 
, COL2 INT NOT NULL 
, COL3 INT NOT NULL 
, COL4 DATE 
, CONSTRAINT THREE_KEYS_PK PRIMARY KEY 
  (
    COL1 
  , COL2 
  , COL3 
  )
  ENABLE 
);
INSERT INTO three_keys VALUES ('A', 1, 2, sysdate);
INSERT INTO three_keys VALUES ('A', 1, 3, sysdate);
INSERT INTO three_keys VALUES ('A', 1, 4, sysdate);
COMMIT:

Now let’s REST enable it and pull up a record.

It’s col1,col2,col3, or ords/hr/three_keys/A,1,2

Related Posts

Tell Others About This Story:

Comments 1

Leave a Reply

Your email address will not be published. Required fields are marked *