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

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

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569