Question

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.

友情链接