I am designing an object-relational model with Oracle (18.4.0) and I would like to add a SCOPE
constraint to a table type column of an object table. Is it possible? Here a simplified model:
CREATE OR REPLACE TYPE t_cycler AS OBJECT (
name VARCHAR2(50)
);
CREATE TABLE cycler OF t_cycler (
name PRIMARY KEY
);
CREATE OR REPLACE TYPE t_cycler_list IS TABLE OF REF t_cycler;
CREATE OR REPLACE TYPE t_team AS OBJECT (
name VARCHAR2(50),
cyclers t_cycler_list
);
CREATE TABLE team OF t_team (
name PRIMARY KEY
)
NESTED TABLE cyclers STORE AS cyclers_tab;
I need that team.cyclers
only contains REF
s to objects in cycler
. I look into the documentation but unfortunately it does not say a lot about SCOPE
constraint, like here:
You can constrain a column type, collection element, or object type attribute to reference a specified object table. Use the SQL constraint subclause SCOPE IS when you declare the REF.
But the only example it provides is about a simple column type. I tried specifying SCOPE IS cycler
in several ways inside the creation of the team
table but with no results.
You want to add the scope to the COLUMN_VALUE
pseudo-column of the nested table:
ALTER TABLE cyclers_tab ADD SCOPE FOR ( COLUMN_VALUE ) IS cycler;
If you then do:
INSERT INTO cycler ( name ) VALUES ( 'c1.1' );
INSERT INTO cycler ( name ) VALUES ( 'c1.2' );
INSERT INTO team (
name,
cyclers
) VALUES (
'team1',
t_cycler_list(
( SELECT REF(c) FROM cycler c WHERE name = 'c1.1' ),
( SELECT REF(c) FROM cycler c WHERE name = 'c1.2' )
)
);
Then you can insert the row. But, if you have another table of the same object type:
CREATE TABLE cycler2 OF t_cycler (
name PRIMARY KEY
);
INSERT INTO cycler2 ( name ) VALUES ( 'c2.1' );
And try to do:
INSERT INTO team (
name,
cyclers
) VALUES (
'team2',
t_cycler_list(
( SELECT REF(c) FROM cycler2 c WHERE name = 'c2.1' )
)
);
Then you get the error:
ORA-22889: REF value does not point to scoped table
db<>fiddle here