[Oracle] Type reference scope

Question

Hello,

I'm studying databases and am currently working on a object-relational DB project and I've encountered a small problem with the number of possible constraints in an object table. I'm using "Database Systems: The Complete Book" by Hector Garcia-Molina (and other authors) as a reference and there's a general SQL example like this:

CREATE TYPE StarType AS (
  name CHAR(30),
  address AddressType,
  bestMovie REF(MovieType) SCOPE Movies
);

Now, I have a kind of a similar type in my project, as it also uses reference to another type within a type, but the clause for placing a reference there doesn't include SCOPE in Oracle (at least I haven't found it in the docs and it outputs an error). So I have a type like this:

CREATE OR REPLACE TYPE "ApplicationType" AS OBJECT (
  "person" REF "PersonType",
  "competition" REF "CompetitionType",
  "dateApplied" DATE
);
/

...which works. But when I want to constrain the REF columns, I can constrain only one, as so:

CREATE TABLE "Applications" OF "ApplicationType" (
  "person" SCOPE IS "People" /* or "competition" SCOPE IS "Competitions" */
)
OBJECT IDENTIFIER IS SYSTEM GENERATED;

Is there any way to give constraints to both REF columns?

Answer 1

This works just fine:

CREATE TABLE Applications OF ApplicationType (
  person SCOPE IS People,
  competition SCOPE IS Competitions
)
OBJECT IDENTIFIER IS SYSTEM GENERATED;

Maybe you tried creating the table using or instead of , for separating the constraints(as seen in your comment).

It's also easy to test your constraints. Just create these two additional dummy tables:

CREATE TABLE People2 OF PersonType
OBJECT IDENTIFIER IS SYSTEM GENERATED;

CREATE TABLE Competitions2 OF CompetitionType
OBJECT IDENTIFIER IS SYSTEM GENERATED;

Then:

INSERT INTO People VALUES('p1');
INSERT INTO People2 VALUES('p21');
INSERT INTO Competitions VALUES('c1');
INSERT INTO Competitions2 VALUES('c21');
COMMIT;
INSERT INTO Applications
VALUES
(
   (SELECT REF(p) FROM People p WHERE person = 'p1'),
   (SELECT REF(c) FROM Competitions2 c WHERE competition = 'c21'),
   SYSDATE
);

results in an ORA-22889 since the refered value is not in the specified scoped table(which is Competitions, not the dummy Competitions2). You can test similarly using People2 instead of People.